Help with Merge and Print

New Contributor

I have a spreadsheet that is used to generate timesheets for our employees, which is Sheet2.  I've linked to our accounting system and populated Sheet1.  This is what I need to happen....for each record in Sheet1 I would like the employees name to appear on the timesheet in Sheet2 and have the timesheet print automatically.  I have no idea how to begin to accomplish this.  Any help would be appreciated.  

5 Replies

@LGray1380 

Let's say the employee names are in A2 and down on Sheet1.

You want the name to appear in C1 on the time sheets.

Macro:

Sub PrintTimeSheets()
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim r As Long
    Dim m As Long
    Set w1 = Worksheets("Sheet1")
    Set w2 = Worksheets("Sheet2")
    m = w1.Range("A" & w1.Rows.Count).End(xlUp).Row
    For r = 2 To m
        Application.StatusBar = "Printing " & r - 1 & " of " & m - 1
        w2.Range("C1").Value = w1.Range("A" & r).Value
        w2.PrintOut
    Next r
    Application.StatusBar = False
End Sub
Now I get "Run-time error '1004': Application-defined or object-defined error on row 8 in macro

@LGray1380 

Are you sure that the error occurs on line 8?

Are Sheet1 and Sheet2 the real names of your two sheets?

Yes, those are the name of the sheets, "Sheet1" has the results of the data query using ODBC with employee name in Column 2; "Sheet2" has the timesheet template. Line 8 is the line it has highlighted.

@LGray1380 

That's weird. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.