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

New Member
Joined
Aug 24, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,823
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
How about:

VBA Code:
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
 
Solution

Frankai

New Member
Joined
Aug 24, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Perfect.. I can't thank you enough !!!!
 

Forum statistics

Threads
1,147,632
Messages
5,742,229
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top