VBA ..Loop through 75 possible rows and copy cells from from one worksheet to another which is a template for a pre-printed form.

[Frankai]

Hi.. I'm new to VBA ... I have a worksheet that has 11 cells of data per row up to 75 rows (max). I want to loop through the rows and copy the cells to a the template worksheet and then print the template for each row. The template worksheet was created using text boxes that match the pre-printed form perfectly. What I don't know is how to setup a loop through all the rows, doing what I just stated above, until I come to an empty row and then I'm done. This is what I have so far to copy the first row of cells to the template and it works fine but I'm sure there is a better way. Any help would be greatly appreciated ! Thank You

Sub Copytotemplate()
Sheets("Template").TextBoxes("TextBox 12").Text = Sheets("Driver list").Range("A3").Value
Sheets("Template").TextBoxes("TextBox 8").Text = Sheets("Driver list").Range("C3").Value
Sheets("Template").TextBoxes("TextBox 1").Text = Sheets("Driver list").Range("E3").Value
Sheets("Template").TextBoxes("TextBox 9").Text = Sheets("Driver list").Range("H3").Value
Sheets("Template").TextBoxes("TextBox 10").Text = Sheets("Driver list").Range("J3").Value
Sheets("Template").TextBoxes("TextBox 11").Text = Sheets("Driver list").Range("L3").Value
Sheets("Template").TextBoxes("TextBox 2").Text = Sheets("Driver list").Range("N3").Value
Sheets("Template").TextBoxes("TextBox 3").Text = Sheets("Driver list").Range("P3").Value
Sheets("Template").TextBoxes("TextBox 4").Text = Sheets("Driver list").Range("R3").Value
Sheets("Template").TextBoxes("TextBox 6").Text = Sheets("Driver list").Range("T3").Value
Sheets("Template").TextBoxes("TextBox 5").Text = Sheets("Driver list").Range("V3").Value
ActiveSheet.PrintOut

End Sub

 

[johnnyL]

How about:

Sub Copytotemplate()
'
    Dim LastRowSourceSheet  As Long
    Dim SourceRowCounter    As Long
    Dim Source              As Worksheet
    Dim Destination         As Worksheet
'
    Set Source = Sheets("Driver list")
    Set Destination = Sheets("Template")
'
    LastRowSourceSheet = Source.Range("A" & Rows.Count).End(xlUp).Row
'
    For SourceRowCounter = 3 To LastRowSourceSheet
        Destination.TextBoxes("TextBox 12").Text = Source.Range("A" & SourceRowCounter).Value
         Destination.TextBoxes("TextBox 8").Text = Source.Range("C" & SourceRowCounter).Value
         Destination.TextBoxes("TextBox 1").Text = Source.Range("E" & SourceRowCounter).Value
         Destination.TextBoxes("TextBox 9").Text = Source.Range("H" & SourceRowCounter).Value
        Destination.TextBoxes("TextBox 10").Text = Source.Range("J" & SourceRowCounter).Value
        Destination.TextBoxes("TextBox 11").Text = Source.Range("L" & SourceRowCounter).Value
         Destination.TextBoxes("TextBox 2").Text = Source.Range("N" & SourceRowCounter).Value
         Destination.TextBoxes("TextBox 3").Text = Source.Range("P" & SourceRowCounter).Value
         Destination.TextBoxes("TextBox 4").Text = Source.Range("R" & SourceRowCounter).Value
         Destination.TextBoxes("TextBox 6").Text = Source.Range("T" & SourceRowCounter).Value
         Destination.TextBoxes("TextBox 5").Text = Source.Range("V" & SourceRowCounter).Value
'
        Destination.PrintOut
    Next
End Sub

 

[Frankai]

Perfect.. I can't thank you enough !!!!

 

[johnnyL]

You are welcome @Frankai. Glad to help!