REALLY need help automating
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.
"Bernie Deitrick" wrote:
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" wrote in message
...
Bernie,
The information is on the same table in the later column, say column K
(1300) , L (1500) & M(1700).
"Bernie Deitrick" wrote:
Cam,
Where is that information in your original table?
Bernie
"Cam" wrote in message
...
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" wrote:
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 ............................................
|