Macro to print series of numbers

[muhleebbin]

Hi,

I have a sheet that prints off certificates that are numbered, however i'm trying to sequence the numbers so that when I cut them they stay in order.

Currently running the following macro:

Sub S_Print()
Dim r As Long

For r = 1 To Worksheets("Stein Certificate").Cells(Worksheets("Stein Certificate").Rows.Count, "O").End(xlUp).Row Step 3
Worksheets("Stein Certificate").Range("C15").Value = Worksheets("Stein Certificate").Cells(r, "O").Value
Worksheets("Stein Certificate").Range("C32").Value = Worksheets("Stein Certificate").Cells(r + 1, "O").Value
Worksheets("Stein Certificate").Range("C49").Value = Worksheets("Stein Certificate").Cells(r + 2, "O").Value
Worksheets("Stein Certificate").PrintOut
Next r
End Sub

and within column o is the following formulas:

=t2
=IF(ROW()<=$T$3,($T$3/3)+1,"")
=IF(ROW()<=$T$3,((($T$3/3)*2)+1),"")
=IF(ROW()<=$T$3,(O1+1),"")
=IF(ROW()<=$T$3,(O2+1),"")
=IF(ROW()<=$T$3,(O3+1),"")

I've dragged rows 4,5,and 6 down to fill up to row 300 (as that's the max i think i'd ever do) but because each row fills with "" the macro will print a bunch of blank pages. Whether I need 21 certificates or 300, is there a way to combine what i'm trying to do in the formula(s) with the print macro above?

Thanks in advance for your help!

 

[Nine Zero]

put an if statement within your for loop something like

[COLOR=#333333]Sub S_Print()[/COLOR]
[COLOR=#333333]Dim r As Long[/COLOR]

[COLOR=#333333]For r = 1 To Worksheets("Stein Certificate").Cells(Worksheets("Stein Certificate").Rows.Count, "O").End(xlUp).Row Step 3[/COLOR]
If Not [COLOR=#333333]Worksheets("Stein Certificate").Cells(r, "O").Value = "" Then
[/COLOR][COLOR=#333333]Worksheets("Stein Certificate").Range("C15").Value = Worksheets("Stein Certificate").Cells(r, "O").Value[/COLOR]
[COLOR=#333333]Worksheets("Stein Certificate").Range("C32").Value = Worksheets("Stein Certificate").Cells(r + 1, "O").Value[/COLOR]
[COLOR=#333333]Worksheets("Stein Certificate").Range("C49").Value = Worksheets("Stein Certificate").Cells(r + 2, "O").Value[/COLOR]
[COLOR=#333333]Worksheets("Stein Certificate").PrintOut
End if
[/COLOR][COLOR=#333333]Next r[/COLOR]
[COLOR=#333333]End Sub

Try that out[/COLOR]

 

[Fluff]

How about

Sub S_Print()
Dim r As Long
With Worksheets("Stein Certificate")
   For r = 1 To Range("O:O").find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row Step 3
      .Range("C15").Value = .Cells(r, "O").Value
      .Range("C32").Value = .Cells(r + 1, "O").Value
      .Range("C49").Value = .Cells(r + 2, "O").Value
      .PrintOut
   Next r
End With
End Sub

 

[muhleebbin]

That makes sense, don't know why I didn't even think of that! This worked perfectly, thank you very much!