Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Print mutiple sheet macro
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to the previous selected sheet in a macro | Excel Discussion (Misc queries) | |||
HELP!! Unhide Sheet with Macro and focus on other sheet | Excel Discussion (Misc queries) | |||
Asked previously...can this not be done in excel | Excel Discussion (Misc queries) | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Print a sheet area to another... | Excel Discussion (Misc queries) |