Hello,

I have a spreadsheet with column for No. (4-numeric digits), model#,
shipdate and six columns for order#. (Example below)
For each No., there are 6 order# assigned and will be filled in. I need to
automate for each No., I need to print out 6 sheets (8-1/2 x 11) with
information that include No., Model and order#. Any help is greatly
appreciated.

No. shipdate model Order#
1001 5/13/08 100 100001 100002 100003 100004 100005
100006
1002 ............................................

Assumptions:
You have a report sheet named "Report"
There are cells on that sheet where you would put the values.
B4 = No.
E6 = Ship Date
D2 = Model

G8 = Order#

Database starts in cell A2 and continues down column A, across the column I.

With the database sheet active, run this:

Sub PrintEmAllOut()
Dim myC As Range
Dim i As Integer

For Each myC In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With Worksheets("Report")
.Range("B4").Value = myC.Value
.Range("E6").Value = myC(1, 2).Value
.Range("D2").Value = myC(1, 3).Value
For i = 1 To 6
.Range("G8").Value = myC(1, i + 3).Value
.PrintOut
Next i
End With
Next myC

End Sub


HTH,
Bernie
MS Excel MVP


"Cam" wrote in message
...
Hello,

I have a spreadsheet with column for No. (4-numeric digits), model#,
shipdate and six columns for order#. (Example below)
For each No., there are 6 order# assigned and will be filled in. I need to
automate for each No., I need to print out 6 sheets (8-1/2 x 11) with
information that include No., Model and order#. Any help is greatly
appreciated.

No. shipdate model Order#
1001 5/13/08 100 100001 100002 100003 100004 100005
100006
1002 ............................................

Bernie,

Thank you for your response, I'll try it tonight. One more question, can you
add a code to ask to print from what No. to No.? Most of the time I might
only print from certain No. only.

"Bernie Deitrick" wrote:

Assumptions:
You have a report sheet named "Report"
There are cells on that sheet where you would put the values.
B4 = No.
E6 = Ship Date
D2 = Model

G8 = Order#

Database starts in cell A2 and continues down column A, across the column I.

With the database sheet active, run this:

Sub PrintEmAllOut()
Dim myC As Range
Dim i As Integer

For Each myC In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With Worksheets("Report")
.Range("B4").Value = myC.Value
.Range("E6").Value = myC(1, 2).Value
.Range("D2").Value = myC(1, 3).Value
For i = 1 To 6
.Range("G8").Value = myC(1, i + 3).Value
.PrintOut
Next i
End With
Next myC

End Sub


HTH,
Bernie
MS Excel MVP


"Cam" wrote in message
...
Hello,

I have a spreadsheet with column for No. (4-numeric digits), model#,
shipdate and six columns for order#. (Example below)
For each No., there are 6 order# assigned and will be filled in. I need to
automate for each No., I need to print out 6 sheets (8-1/2 x 11) with
information that include No., Model and order#. Any help is greatly
appreciated.

No. shipdate model Order#
1001 5/13/08 100 100001 100002 100003 100004 100005
100006
1002 ............................................

Cam,

If your Numbers are integers....

HTH,
Bernie
MS Excel MVP


Sub PrintMostOut()
Dim myC As Range
Dim i As Integer
Dim iStart As Integer
Dim iEnd As Integer

iStart = CInt(InputBox("What start No?"))
iEnd = CInt(InputBox("What end No?"))

For Each myC In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If myC.Value = iStart And myC.Value <= iEnd Then
With Worksheets("Report")
.Range("B4").Value = myC.Value
.Range("E6").Value = myC(1, 2).Value
.Range("D2").Value = myC(1, 3).Value
For i = 1 To 6
.Range("G8").Value = myC(1, i + 3).Value
.PrintOut
Next i
End With
End If
Next myC

End Sub


"Cam" wrote in message
...
Bernie,

Thank you for your response, I'll try it tonight. One more question, can
you
add a code to ask to print from what No. to No.? Most of the time I might
only print from certain No. only.

"Bernie Deitrick" wrote:

Assumptions:
You have a report sheet named "Report"
There are cells on that sheet where you would put the values.
B4 = No.
E6 = Ship Date
D2 = Model

G8 = Order#

Database starts in cell A2 and continues down column A, across the column
I.

With the database sheet active, run this:

Sub PrintEmAllOut()
Dim myC As Range
Dim i As Integer

For Each myC In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With Worksheets("Report")
.Range("B4").Value = myC.Value
.Range("E6").Value = myC(1, 2).Value
.Range("D2").Value = myC(1, 3).Value
For i = 1 To 6
.Range("G8").Value = myC(1, i + 3).Value
.PrintOut
Next i
End With
Next myC

End Sub


HTH,
Bernie
MS Excel MVP


"Cam" wrote in message
...
Hello,

I have a spreadsheet with column for No. (4-numeric digits), model#,
shipdate and six columns for order#. (Example below)
For each No., there are 6 order# assigned and will be filled in. I need
to
automate for each No., I need to print out 6 sheets (8-1/2 x 11) with
information that include No., Model and order#. Any help is greatly
appreciated.

No. shipdate model Order#
1001 5/13/08 100 100001 100002 100003 100004 100005
100006
1002 ............................................

Bernie,

Sorry to get back late. It worked like charm. Thank you.
I was just wondering too if you could add in the code to instead of
selecting the from and to range, it make so I can select number of line to
print (for example: 1001, 1030, 1031, etc..). And is there a way to add a
confirming message of what is going to be printed before printing it to a
printer. Thanks again.

"Bernie Deitrick" wrote:

Cam,

If your Numbers are integers....

HTH,
Bernie
MS Excel MVP


Sub PrintMostOut()
Dim myC As Range
Dim i As Integer
Dim iStart As Integer
Dim iEnd As Integer

iStart = CInt(InputBox("What start No?"))
iEnd = CInt(InputBox("What end No?"))

For Each myC In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If myC.Value = iStart And myC.Value <= iEnd Then
With Worksheets("Report")
.Range("B4").Value = myC.Value
.Range("E6").Value = myC(1, 2).Value
.Range("D2").Value = myC(1, 3).Value
For i = 1 To 6
.Range("G8").Value = myC(1, i + 3).Value
.PrintOut
Next i
End With
End If
Next myC

End Sub


"Cam" wrote in message
...
Bernie,

Thank you for your response, I'll try it tonight. One more question, can
you
add a code to ask to print from what No. to No.? Most of the time I might
only print from certain No. only.

"Bernie Deitrick" wrote:

Assumptions:
You have a report sheet named "Report"
There are cells on that sheet where you would put the values.
B4 = No.
E6 = Ship Date
D2 = Model

G8 = Order#

Database starts in cell A2 and continues down column A, across the column
I.

With the database sheet active, run this:

Sub PrintEmAllOut()
Dim myC As Range
Dim i As Integer

For Each myC In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With Worksheets("Report")
.Range("B4").Value = myC.Value
.Range("E6").Value = myC(1, 2).Value
.Range("D2").Value = myC(1, 3).Value
For i = 1 To 6
.Range("G8").Value = myC(1, i + 3).Value
.PrintOut
Next i
End With
Next myC

End Sub


HTH,
Bernie
MS Excel MVP


"Cam" wrote in message
...
Hello,

I have a spreadsheet with column for No. (4-numeric digits), model#,
shipdate and six columns for order#. (Example below)
For each No., there are 6 order# assigned and will be filled in. I need
to
automate for each No., I need to print out 6 sheets (8-1/2 x 11) with
information that include No., Model and order#. Any help is greatly
appreciated.

No. shipdate model Order#
1001 5/13/08 100 100001 100002 100003 100004 100005
100006
1002 ............................................

Cam,

If you want a single line, simply use the 1001 (for example) as both the starting and ending number.

To confirm the prinout, use

If MsgBox("Print it?", vbYesNo) = vbYes Then .PrintOut

instead of just

.PrintOut

HTH,
Bernie
MS Excel MVP


"Cam" wrote in message
...
Bernie,

Sorry to get back late. It worked like charm. Thank you.
I was just wondering too if you could add in the code to instead of
selecting the from and to range, it make so I can select number of line to
print (for example: 1001, 1030, 1031, etc..). And is there a way to add a
confirming message of what is going to be printed before printing it to a
printer. Thanks again.

"Bernie Deitrick" wrote:

Cam,

If your Numbers are integers....

HTH,
Bernie
MS Excel MVP


Sub PrintMostOut()
Dim myC As Range
Dim i As Integer
Dim iStart As Integer
Dim iEnd As Integer

iStart = CInt(InputBox("What start No?"))
iEnd = CInt(InputBox("What end No?"))

For Each myC In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If myC.Value = iStart And myC.Value <= iEnd Then
With Worksheets("Report")
.Range("B4").Value = myC.Value
.Range("E6").Value = myC(1, 2).Value
.Range("D2").Value = myC(1, 3).Value
For i = 1 To 6
.Range("G8").Value = myC(1, i + 3).Value
.PrintOut
Next i
End With
End If
Next myC

End Sub


"Cam" wrote in message
...
Bernie,

Thank you for your response, I'll try it tonight. One more question, can
you
add a code to ask to print from what No. to No.? Most of the time I might
only print from certain No. only.

"Bernie Deitrick" wrote:

Assumptions:
You have a report sheet named "Report"
There are cells on that sheet where you would put the values.
B4 = No.
E6 = Ship Date
D2 = Model

G8 = Order#

Database starts in cell A2 and continues down column A, across the column
I.

With the database sheet active, run this:

Sub PrintEmAllOut()
Dim myC As Range
Dim i As Integer

For Each myC In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With Worksheets("Report")
.Range("B4").Value = myC.Value
.Range("E6").Value = myC(1, 2).Value
.Range("D2").Value = myC(1, 3).Value
For i = 1 To 6
.Range("G8").Value = myC(1, i + 3).Value
.PrintOut
Next i
End With
Next myC

End Sub


HTH,
Bernie
MS Excel MVP


"Cam" wrote in message
...
Hello,

I have a spreadsheet with column for No. (4-numeric digits), model#,
shipdate and six columns for order#. (Example below)
For each No., there are 6 order# assigned and will be filled in. I need
to
automate for each No., I need to print out 6 sheets (8-1/2 x 11) with
information that include No., Model and order#. Any help is greatly
appreciated.

No. shipdate model Order#
1001 5/13/08 100 100001 100002 100003 100004 100005
100006
1002 ............................................