Macro/VBA suggestion needed

[Flora2021]

Hello, I have a spreadsheet (sheet1)that we need to print for approximately 90 employees. On this sheet there is a name, 3 different department fields, and a date range field.
I have another sheet (Sheet2) that lists all of this data in separate columns. I would like to be able to copy and paste each of the columns into the fields on sheet one and then print and repeat. I know I can create a macro to do this function, but I don't want to repeat it each time for 90 people. Is there a way to make it look at next row and repeat the steps?

 

[Puertorekinsam]

this should work for you


Sub fill_and_print()
i = 2
Do Until Sheet2.Cells(i, 1) = ""
For j = 1 To 5
If Sheet2.Cells(i, j) = "" Then Sheet1.Cells(j, 2) = "" Else Sheet1.Cells(j, 2) = Sheets.Cells(i, j)
Next

Sheet1.PrintOut
i=i+1

Loop
End Sub

 

[Flora2021]

Thanks, I received this error.

 

[Flora2021]

This is the macro I created that works, but only for the one record

Sub Training_Records()
'
' Training_Records Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Sheets("Sheet2").Select
Range("A2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B2:D2").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B3:D3").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B4:D4").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B5:D5").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("C6:D6").Select
ActiveSheet.Paste
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub

 

[Puertorekinsam]

woops sorry, there was a 2 missing (also make sure you have the i=i+1 line before the loop)

Sub fill_and_print()
i = 2
Do Until Sheet2.Cells(i, 1) = ""
For j = 1 To 5
If Sheet2.Cells(i, j) = "" Then Sheet1.Cells(j, 2) = "" Else Sheet1.Cells(j, 2) = Sheets2.Cells(i, j)
Next

Sheet1.PrintOut
i=i+1

Loop
End Sub

 

[Flora2021]

I get this new error

 

[Puertorekinsam]

this is what I get for not fully testing because I didn't want to print....

Sub fill_and_print()
i = 2
Do Until Sheet2.Cells(i, 1) = ""
For j = 1 To 5
If Sheet2.Cells(i, j) = "" Then Sheet1.Cells(j, 2) = "" Else Sheet1.Cells(j, 2) = Sheet2.Cells(i, j)
Next

Sheet1.PrintOut
i = i + 1

Loop
End Sub

 

[Flora2021]

Hi, Its very close LOL
the first record works perfect but the other records are skipping the Name, and double posting the training period and placing the depts in wrong fields.

 

[Puertorekinsam]

alright one last shot... I didn't notice the blank row 1, and the training records being put in column C...if this doesn't work I give up

Sub fill_and_print()
i = 2
Do Until Sheet2.Cells(i, 1) = ""
For j = 1 To 4
    If Sheet2.Cells(i, j) = "" Then Sheet1.Cells(j + 1, 2) = "" Else Sheet1.Cells(j + 1, 2) = Sheet2.Cells(i, j)
Next
If Sheet2.Cells(i, j) = "" Then Sheet1.Cells(j + 1, 3) = "" Else Sheet1.Cells(j + 1, 3) = Sheet2.Cells(i, j)
Sheet1.PrintOut
i = i + 1

    Loop
End Sub

 

[Alex Blakenburg]

Just a slight modification to @Puertorekinsam's Post #7 should get you there:

Sub fill_and_print()
    Dim i As Long, j As Long
    i = 2
    Do Until Sheet2.Cells(i, 1) = ""
        For j = 1 To 5
            Sheet1.Cells(j + 1, 2) = Sheet2.Cells(i, j)
        Next
        
        Sheet1.PrintOut
        i = i + 1
    Loop
End Sub