Looping and Repeat Proceedure

[Hantzisp]

Hello everyone. I need help figuring out how to repeat a procedure I created.

Here’s the code:

Sub osvPrint()

Dim osv As Worksheet
Dim  vosv As Worksheet
Set osv = Sheets("OSV")
Set vosv = Sheets("vosv")


vosv.Shapes("Textbox 5").TextFrame.Characters.Text = osv.Range("D5")
vosv.Shapes("Textbox 6").TextFrame.Characters.Text = osv.Range("E5")
vosv.Shapes("Textbox 8").TextFrame.Characters.Text = osv.Range("F5")
vosv.Shapes("Textbox 9").TextFrame.Characters.Text = osv.Range("G5")
vosv.Shapes("Textbox 10").TextFrame.Characters.Text = osv.Range("H5")
vosv.Shapes("Textbox 11").TextFrame.Characters.Text = osv.Range("I5")
vosv.Shapes("Textbox 12").TextFrame.Characters.Text = osv.Range("J5")
vosv.Shapes("Textbox 13").TextFrame.Characters.Text = osv.Range("L5")
vosv.Shapes("Textbox 17").TextFrame.Characters.Text = osv.Range("H5")
vosv.Shapes("Textbox 26").TextFrame.Characters.Text = osv.Range("H5")
vosv.Shapes("Textbox 16").TextFrame.Characters.Text = osv.Range("B5")
vosv.Shapes("Textbox 18").TextFrame.Characters.Text = osv.Range("B5")
vosv.Shapes("Textbox 19").TextFrame.Characters.Text = osv.Range("B5")
vosv.Shapes("Textbox 20").TextFrame.Characters.Text = osv.Range("B5")
vosv.Shapes("Textbox 21").TextFrame.Characters.Text = osv.Range("C5")
vosv.Shapes("Textbox 22").TextFrame.Characters.Text = osv.Range("C5")
vosv.Shapes("Textbox 23").TextFrame.Characters.Text = osv.Range("N5")
vosv.Shapes("Textbox 24").TextFrame.Characters.Text = osv.Range("Q5")
vosv.Shapes("Textbox 25").TextFrame.Characters.Text = osv.Range("P5")

vosv.PrintOut


End Sub

The OSV worksheet is basically a roster. The vosv worksheet is a form. The code grabs the info from the first entry on the OSV worksheet and puts the info in the textbox fields on the vosv worksheet form and prints the form. This works for the first entry, but I need to repeat the process for each entry past row 5 on the roster until there are no more entries. I’m going to add a print button to run the procedure once the roster is filled out.

So once I click the print button, it should:

Look a cell A5. If there’s an entry it should run the procedure and print the form. Then, look in cell A6. If there’s an entry, run procedure and print form with the data in row 6. But, my code is referencing cells in row 5. How do I get it to go from this:

vosv.Shapes("Textbox 5").TextFrame.Characters.Text = osv.Range("D5")

to look at cell A6 and if there’s an entry, run procedure but change Range to row 6's like this:

vosv.Shapes("Textbox 5").TextFrame.Characters.Text = osv.Range("D6")

Just using the first line of code from the proceedure, but the Range should continue (e.g. D5, E5, F5 etc... to D6, E6, F6 etc...). It's just a little beyond my skill set at the moment.

 

[Fluff]

How about

Sub osvPrint()

Dim osv As Worksheet
Dim vosv As Worksheet
Dim i As Long

Set osv = Sheets("OSV")
Set vosv = Sheets("vosv")

For i = 5 To osv.Range("D" & Rows.Count).End(xlUp).Row
   vosv.Shapes("Textbox 5").TextFrame.Characters.Text = osv.Range("D" & i)
   vosv.Shapes("Textbox 6").TextFrame.Characters.Text = osv.Range("E" & i)
   vosv.Shapes("Textbox 8").TextFrame.Characters.Text = osv.Range("F" & i)
   vosv.Shapes("Textbox 9").TextFrame.Characters.Text = osv.Range("G" & i)
   vosv.Shapes("Textbox 10").TextFrame.Characters.Text = osv.Range("H" & i)
   vosv.Shapes("Textbox 11").TextFrame.Characters.Text = osv.Range("I" & i)
   vosv.Shapes("Textbox 12").TextFrame.Characters.Text = osv.Range("J" & i)
   vosv.Shapes("Textbox 13").TextFrame.Characters.Text = osv.Range("L" & i)
   vosv.Shapes("Textbox 17").TextFrame.Characters.Text = osv.Range("H" & i)
   vosv.Shapes("Textbox 26").TextFrame.Characters.Text = osv.Range("H" & i)
   vosv.Shapes("Textbox 16").TextFrame.Characters.Text = osv.Range("B" & i)
   vosv.Shapes("Textbox 18").TextFrame.Characters.Text = osv.Range("B" & i)
   vosv.Shapes("Textbox 19").TextFrame.Characters.Text = osv.Range("B" & i)
   vosv.Shapes("Textbox 20").TextFrame.Characters.Text = osv.Range("B" & i)
   vosv.Shapes("Textbox 21").TextFrame.Characters.Text = osv.Range("C" & i)
   vosv.Shapes("Textbox 22").TextFrame.Characters.Text = osv.Range("C" & i)
   vosv.Shapes("Textbox 23").TextFrame.Characters.Text = osv.Range("N" & i)
   vosv.Shapes("Textbox 24").TextFrame.Characters.Text = osv.Range("Q" & i)
   vosv.Shapes("Textbox 25").TextFrame.Characters.Text = osv.Range("P" & i)
   
   vosv.PrintOut
Next i

End Sub

 

[Hantzisp]

That's it! I changed:

For i = 5 To osv.Range("D" & Rows.Count).End(xlUp).Row

to

For i = 5 To osv.Range("A" & Rows.Count).End(xlUp).Row

Because I wanted it looking to see if there's data in Column A. But, I understand what and how to do it now. I'm learning. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback