Printing Next Row

[Many thanks Sue]

Good Morning

First time post so if I get it all wrong please allow a little leeway.

We have a UserForm that reads a Result Sheet on filling the Text Boxes and
clicking a CommandButton it the enters the info on a sheet named Card - we
then print the resulting card at the moment we have to do each one
individually by selecting a number from 1-100 from a drop down Combo Box is
it possible after selecting 1 from the drop down it will then continue to
find the next row down and print all the rows on to an individual card. The
snippets of code below are I suspect very simple to all you clever people out
there but this is we do it at the moment and its very time consuming.

Private Sub Add1_Click()

Dim varAnswer As String

varAnswer = MsgBox("Have you added the Dialog Box Entries to the Sheet?",
vbYesNo, "Print Warning")
If varAnswer = vbNo Then
Exit Sub
End If
Dim X As Integer
X = InputBox("Number Of Copies Required ? Enter In Box Below", "Print")
If X > 0 Then
Sheets("Card").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=X, Collate:=True
End If
End Sub


Private Sub Cmb10_Click()

Dim startrownum As Integer
Dim endrownum As Integer
startrownum = 2
endrownum = 102

UserForm1.Tb52.Value = (Sheets("Result").Range("A" &
Trim(Str(Cmb10.ListIndex + startrownum))))
UserForm1.Tb53.Value = (Sheets("Result").Range("B" &
Trim(Str(Cmb10.ListIndex + startrownum))))
UserForm1.Tb54.Value = (Sheets("Result").Range("C" &
Trim(Str(Cmb10.ListIndex + startrownum))))
UserForm1.Tb55.Value = (Sheets("Result").Range("D" &
Trim(Str(Cmb10.ListIndex + startrownum))))
UserForm1.Tb56.Value = (Sheets("Result").Range("E" &
Trim(Str(Cmb10.ListIndex + startrownum))))
UserForm1.TB57.Value = (Sheets("Result").Range("F" &
Trim(Str(Cmb10.ListIndex + startrownum))))
UserForm1.Tb58.Value = (Sheets("Result").Range("G" &
Trim(Str(Cmb10.ListIndex + startrownum))))


End Sub
Private Sub Add11_Click()

Worksheets("Card").Range("I44").Value = UserForm1.Tb56.Value
Worksheets("Card").Range("B6:W6").Value = UserForm1.Tb53.Value
Worksheets("Card").Range("E10:I10").Value = UserForm1.Tb52.Value
Worksheets("Card").Range("H1313").Value = UserForm1.Cmb11.Value
Worksheets("Card").Range("I15").Value = UserForm1.Tb54.Value
Worksheets("Card").Range("M15:O15").Value = UserForm1.Tb55.Value
Worksheets("Card").Range("J17:N17").Value = UserForm1.TB57.Value
Worksheets("Card").Range("Q11:W11").Value = UserForm1.Tb51.Value
Worksheets("Card").Range("J10:M10").Value = UserForm1.Tb58.Value




End Sub

 

[Bernie Deitrick]

Sue,

The typical way to do this is to increment through your data base, updating the cells on the
printout sheet each time, then printing. Based on your sample code, I think the macro below should
work for you. It wasn't clear from your code what the values of

UserForm1.Cmb11.Value
and
UserForm1.Tb51.Value

should be, so you'll need to adjust those lines. I asumed that you want all the data from sheet
"Result" printed out.

HTH,
Bernie
MS Excel MVP


Sub PrintOutForSue()
Dim X As Integer

Dim StartRowNum As Integer
Dim EndRowNum As Integer

StartRowNum = 2
EndRowNum = Sheets("Result").Range("E65536").End(xlUp).Row

For X = StartRowNum To EndRowNum
Worksheets("Card").Range("I44").Value = Sheets("Result").Range("E" & X)
Worksheets("Card").Range("B6:W6").Value = Sheets("Result").Range("B" & X)
Worksheets("Card").Range("E10:I10").Value = Sheets("Result").Range("A" & X)
Worksheets("Card").Range("H1313").Value = UserForm1.Cmb11.Value
Worksheets("Card").Range("I15").Value = Sheets("Result").Range("C" & X)
Worksheets("Card").Range("M15:O15").Value = Sheets("Result").Range("D" & X)
Worksheets("Card").Range("J17:N17").Value = Sheets("Result").Range("F" & X)
Worksheets("Card").Range("Q11:W11").Value = UserForm1.Tb51.Value
Worksheets("Card").Range("J10:M10").Value = Sheets("Result").Range("G" & X)
Worksheets("Card").PrintOut
Next X


End Sub

 

[Tom Ogilvy]

This assumes that when the value is changed for cmb10, it updates the Card
sheet with the appropriate data. Add a button Add2:

Private Sub Add2_Click()

Dim varAnswer As String

varAnswer = MsgBox("Have you added the Dialog Box Entries to the Sheet?",
vbYesNo, "Print Warning")
If varAnswer = vbNo Then
Exit Sub
End If
Dim i As Long
for i = 0 to cmb10.Listcount - 1
cmb10.ListIndex = i
Sheets("Card").PrintOut Copies:=1, Collate:=True
Next
End Sub

 

[Sue]

Hi Gentlemen

You are brilliant as the hours passed I thought it wasn't going to be
possible what we needed. Last year I printed nearly 1200 of these cards each
one individually, just over 50 a week for 22 weeks during our season - two
years ago I wrote them all by hand and then asked our Chairman and Secretary
to sign them all - they are Prize Cards for our Gardening Society. Excel
works wonders -- just one question Bernie is it possible to stop the routine
running at say row 5 so that I can check that eveything is OK before running
off the full compliment of Cards.

 

[Bernie Deitrick]

Sue,

Right after this line:

For X = StartRowNum To EndRowNum

Add this line

If X = 5 Then If MsgBox("Continue, Sue?", vbYesNo) = vbNo Then Exit Sub

and if you click yes it will continue, no will stop.

HTH,
Bernie
MS Excel MVP

 

[Sue]

Hi Bernie

Spot on -- even my Chairman of the Society is impressed and that takes some
doing.