REALLY need help automating

[Cam]

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 Deitrick]

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]

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]

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]

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]

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]

Bernie,

Thanks again, but I have no more request.
Can you add in the code to print an additional information, part#?

No. shipdate model Order# Part#
1001 5/13/08 100 100001 X X X X X 1300 1500 1700
1002 .........

where 1300 column is for Order# 100001 & 100002, 1500 is for 100003 & 100004
and 1700 for 100005 & 100006 on the No. 1001 same row.

 

[Bernie Deitrick]

Cam,

Where is that information in your original table?

Bernie

 

[Cam]

Bernie,

The information is on the same table in the later column, say column K
(1300) , L (1500) & M(1700).

 

[Bernie Deitrick]

Perhaps...

For i = 1 To 6
.Range("G8").Value = myC(1, i + 3).Value
'Change the H8 to the address of the cell where you want the values
'from columsn K, L, and M
.Range("H8")..Value = myC(1, (i + 1) \ 2 + 10).Value
.PrintOut
Next i


HTH,
Bernie
MS Excel MVP

 

[Cam]

Bernie,

Thanks so much, everything working great so far.
Can you please add to the code so that if any of the column D to I is blank,
then don't print the report page for that blank cell. Ex:

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

So, when I print line No. 1001, it skip the page for cell F2 and only print
a report sheet for order# 100001, 100002, 100004, 100005 and 100006.