ExcelBanter - Print mutiple sheet macro

You ask the user to furnish a Start Job Number (sname) and a Finish Job
Number (sname2). What is the significance of the Job Number to the sheets
you want printed? Does every sheet contain a job number? In what cell?
The same cell on every sheet? If every sheet has a job number and you want
to print every sheet from this job number to that job number, then your
macro has to scan all the sheets and look for a job number that is in that
bracket of job numbers, and print those sheets that are. Is this what you
want?
You don't specify a range on each sheet to print. You just say to print
the sheet. This can result on a lot of blank pages. It's best if specify a
specific range. The range can different for each sheet if you have a way of
determining the range.
Look at the following macro and see if it helps. Post back if you need
more. I assumed the Job Number is in A1 of every sheet. HTh Otto
Sub doprint()
Dim First As Long
Dim Last As Long
Dim ws As Worksheet
First = InputBox("Start in Job Number?", "First Job to Print", 0)
Last = InputBox("Finish in Job Number?", "Last Job to Print", 0)
For Each ws In ActiveWorkbook.Worksheets
With ws
If .[A1].Value = First And .[A1].Value <= Last Then _
.Range("A2:H50").PrintOut
End With
Next ws
End Sub
"Razzcul" wrote in
message ...

I have a excel workbook with as many a 2 to 25 pages and can still grow.
I use VLOOKUP on the second sheet to grab info form the first. Sheets 3
thru end with all populate based on sheet before. I have a formula on
sheet 1 that determines how many sheets need to be print. How can I put
this into a macro?

Here is what I have now. This only print 1 page per counter loop.

Sub doprint()
'
' doprint Macro
' Macro recorded 9/25/2003 by AvilaJ

Dim i As Integer
Dim oCell As Range

sname = InputBox("Start in Job Number?", " First Job to Print", 0)
sname2 = InputBox("Finish in Job Number?", " Last Job to Print",
0)

Range("I40").Select
ActiveCell.FormulaR1C1 = sname
Range("I41").Select
ActiveCell.FormulaR1C1 = sname2

For Counter = sname To sname2
Range("L5").Select
ActiveCell.FormulaR1C1 = Counter
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1,
Collate _
:=True
Next Counter

End Sub

--
Razzcul
------------------------------------------------------------------------
Razzcul's Profile:
http://www.excelforum.com/member.php...o&userid=37365
View this thread: http://www.excelforum.com/showthread...hreadid=570574