Dynamic Print area based on range typed into a cell

mbarbera83

New Member
Joined
Feb 17, 2015
Messages
27
Is there a way that i can set the print area based on the INDIRECT function of a range typed into a cell.

i have a worksheet with 52 sheets.
i would like to run a Macro that runs through all the sheets and sets the Print area based on a cell in that sheet, then print that report
Then run another macro that sets print area to a different area on that same sheet and then print that.

1 report is all the pages, while other report is pages 1 and the last page. perhaps easier way to set to print all, but then a macro to print page 1 and last page of each sheet?

Any help would be appreciated.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
perhaps easier way to set to print all, but then a macro to print page 1 and last page of each sheet?

Hi - you could try:

Code:
Sub m1()
Dim ws As Worksheet, lp As Long
For Each ws In ThisWorkbook.Worksheets 'Loop thru sheets and print all pages
    ws.PrintOut
Next ws
For Each ws In ThisWorkbook.Worksheets 'Loop thru sheets and print First & last page
    lp = (ws.HPageBreaks.Count + 1) * (ws.VPageBreaks.Count + 1)
    ws.PrintOut From:=1, To:=1
    If lp > 1 Then
        ws.PrintOut From:=lp, To:=lp
    End If
Next ws
End Sub
 
Upvote 0
Hi - is there a way that I can customize this, that it prints all the pages to 1 PDF file .

i am currently using the following code and "hard coding" the sheet names into the code when i want to select several sheets and print to 1 PDF.

Application.ActivePrinter = "CutePDF Writer on CPW2:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True
 
Upvote 0
Hi, sounds like a completely different question? And the code does appear to have any sheet names hard coded?
 
Upvote 0
my original plan was to create a macro that set print area on each sheet for Managers (pages 1 to 11 (11 could be 10 or 12, depending on the amount of info on that sheet) per sheet, with some managers receiving more than one sheet)
and then to set another macro to set the print area on each sheet for Directors (pages 1 and the last page of each sheet, Directors to receive all sheets, but only 1st and last page per sheet)

then asked a side question regarding perhaps just printing page 1 and last page per sheet, which you have answered, but ideally i would like one file for the directors.

as for the hardcoding - here is an example of code used to print for 1 manager - printing 4 sheets
Sheets(Array("2705", "2725", "2726", "2727")).Select
Sheets("2705").Activate
Application.ActivePrinter = "CutePDF Writer on CPW2:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True
Sheets("Summary").Select
 
Upvote 0
So instead of the hard coding, how would we determine which sheets need to printed? If you want to manually select the sheets; remove these two lines, hold control and click on the sheets you want to print and run the macro.

Rich (BB code):
Sheets(Array("2705", "2725", "2726", "2727")).Select
Sheets("2705").Activate
 
Upvote 0
i have created several macros that selects sheets per manager
in that macro i then run the print to pdf macro

the thing that i want to make dynamic, is the print area per sheet
i would ideally like to have a cell on each sheet, where i would have to type in (or at a later stage have a formula) the desired print area - as this is dependant on certain variables, and varies per sheet. - i am prepared to do this manaully, as is it quicker than setting the print area bigger and then having to go to each sheet and then click and drag the print area page breaks as required.

my though originally was to have a macro that sets the print area per an activated sheet based on an INDIRECT(B11) for example, but this did not seem to work.
 
Upvote 0
So what cell on the sheet, what are the values can be in that cell and how do those values translate into the print area range?
 
Upvote 0
i would like to have a macro that runs the following:

ActiveSheet.PageSetup.PrintArea = "$E$5:$L$27,$Q$30:$X$44"

ideally what i would like to do is in B11 of each sheet, to type in $E$5:$L$27,$Q$30:$X$44

as on 2725 the print area is $E$5:$L$27,$Q$30:$X$44, while on
2730 (a different sheet) $E$5:$L$55,$Q$30:$X$35
 
Upvote 0
OK - give this a try, only very lightly tested:

Code:
Sub m2()
Dim v, i As Long

v = Array("2705", "2725", "2726", "2727")
For i = 0 To UBound(v)
    Sheets(v(i)).PageSetup.PrintArea = Sheets(v(i)).Range("B11").Value
Next i

Application.ActivePrinter = "CutePDF Writer on CPW2:" 'Do you really need this line??
Sheets(v).PrintOut Copies:=1, ActivePrinter:="CutePDF Writer on CPW2:", Collate:=True
Sheets("Summary").Select

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top