Type in two sheets at once

[raywilexel]

I'm a newbie at forums and VBA. I've been working a short time with VBA and like it. I want to type info into a row of cells in Sheet 1. Have that same info go into various cells in Sheet 2(A form that will be printed.). Once the last cell in Sheet 1 is typed have the filled row move down leaving a new blank row for the next set of entries starting the process over. It would be nice to have the form print at the end of data entry. I hope I've been clear and thanks for the help.

[mikerickson]

When you go to the new row, what do you want to happen in Sheet2?

The user is typing in Sheet1.
They put data in A1 and that data is copied into Sheet2!A1
They put data is B1 and that data is copied into Sheet2!C3
The press Return, and the data moves from Sheet1A1:B1 to Sheet1A2:B2 and the cursor moves to Sheet1A1
What happens to the data in Sheet2?

[raywilexel]

The form(Sheet2) prints. Thanks

[mikerickson]

When does it print?
Do you want the user to tell it to print? or do you want it to be automatic when Sheet1! row 1 is completely filled out.
Do you want the user to have a chance to abort the printing?

It almost sounds like the logic could be
"Every time that Sheet2 is printed, clear Sheet2, move Sheet1's data down a row and move the cursor to Sheet1!A1"

[raywilexel]

Your logic statement is correct. The user will need to initiate printing, with abort option. Not all cells will have input. Some instances only certain cells will have info placed in them. I'm not sure if the user tabs through each cell and when the last one is reached the print option comes into play. I'm familiarizing myself with some of the commands in your code to get a better grip on their use. I tried at first to do this as a recorded macro...didn't work so well. Thanks again for the lesson. I'll walk though the code to get a better sense...

[mikerickson]

If you have formulas like =Sheet1$A$1 in the cells of Sheet2.
And if a row is filled when there is data in all of the cells of A1:I1, you could put this in the code module for Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Range("A1:I1")
        If Application.CountA(.Cells) = .Cells.Count Then Call PrintForm
    End With
End Sub

Sub PrintForm()
    Sheets("Sheet2").Activate
    If Application.Dialogs(xlDialogPrint).Show(2, 1, 1, 1) Then
        With Sheets("Sheet1")
            .Unprotect
            With Application.Intersect(.UsedRange, .Range("A:AZ"))
                .Offset(1, 0).Value = .Value
                .Offset(1, 0).Locked = True
                
                With .Rows(1)
                    .ClearContents
                    .Locked = False
                End With
            End With
            .Protect
        End With
    Else
        MsgBox "Form not printed. Data not moved."
    End If
    Application.Goto Sheets("Sheet1").Range("A1")
End Sub

[mikerickson]

As written, this will start the process when all of the cells in A1:I1 are filled.

If you want the user to press a PrintButton, do not use the Change event code.

Put the PrintForm code in a normal module and either have the user call it from the dialog box or assign it to a button or one of the other ways for the user to trigger a sub.

[raywilexel]

Thanks I've got some studying to do to better understand the different types of modules, this is a great start. I think a button will be the direction to go. I could use an example of the replacement code.

[mikerickson]

Its the same code as the sub PrintForm that is above.

[raywilexel]

I did some work with code now and see. Thanks again