Set Print Area Across Multiple Sheets

Topic

New Reply

Excel 97 does not have the capability to select a print area across multiple sheets, unless I’ve seriously overlooked something. Try it for your self, select multiple sheets in a workbook, select a range (larger than one cell) within all those sheets; you will find that the File, Page Setup, Sheet, Print Area dialog is grayed out (if you use the Set Print Area icon, it’s grayed out).

I frequently have workbooks with multiple sheets with an identical layout, and would like to select the print area on all of them with one keypress or click. But I’d like this to be specific to only the selected sheets, as within such a workbook I often have source data or other sheets with a different print layout. I don’t have the skill to write code for this. Call me the VBA mendicant (some say miscreant).

Can someone direct me to a solution, or provide a macro to answer my prayers? Is this resolved in Excel2000?

Reply | Quote

Replies

I’m not sure I understand you problem but think you would like to set up identical sheets with the same Page Settings, including Print Area.

I dont know about one keypress solutions, but a quick way I use is to set one of the sheets the way I want it and immediately that is done select what ever other sheets I need and press F4. In fact you can just go trough the motions of page SetUp and then repeat (F4) for any sheet you want to have the same Page SetUp

regards,

Andrew

Reply | Quote

Sounds like you understand the problem, and your solution is almost what I’m doing. (Try not to laugh too much.) I usually select the range throughout the selected sheets. Then I have to deselect the sheets and go to the first one and click the SetPrintArea icon. (All the target sheets retain the selected range.) Then, right hand on mouse, pointer over the SetPrintArea icon, left hand on the right side of the keyboard, I Ctrl-PageDn through the required sheets, while the right hand clicks the icon each time.

I do one like this about every fortnight and I’m just sick of doing it 43 times for my usual 43 market areas! Carpal Tunnel Syndrome!

Reply | Quote

Just to make sure you are not doing too much clicking.

If You have a work sheet with Page Setup the way you want, all you need do is select that sheet, goto File | Page Setup and just click on any setting without changing it. Then seclect OK.

Then click on the tabs of the other sheets with the same setup and press F4. There is no need to select ranges. Hope that makes it easier for you.

That works in XL 97 and 2000

Andrew C

Reply | Quote

The following VBA routine will set the print area for worksheet Sheet1 to the range $A$1:$C$5. You just need to add similar statements for all of the sheets for which you want to set the print area. I don’t see any way to have a routing identify the selected sheets and do it for them. You could pick up the selection from the active sheet to determine what range to set.

Public Sub SetPrintArea()
    Worksheets("Sheet1").PageSetup.PrintArea = "$A$1:$C$5"
End Sub

Reply | Quote

how about something along the lines of:

For Each sh In Workbooks(“BOOK1.XLS”).Windows(1).SelectedSheets
sh.PageSetup.PrintArea = “$A$1:$C$5”
Next

Would that work?

Brooke

Reply | Quote

Yes, thanks to Bill Manville over on CompuServe I found the SelectedSheets collection and have posted a macro in another reply in this thread.

Reply | Quote

Thanks to Bill Manville in the CompuServe Excel forum, I have found the collection of selected sheets. The following VBA routine will set the print area on all selected sheets to the current selection.

Public Sub SetPrintAreas()
Dim oSheet As Worksheet
Dim sPrintRange As String
    sPrintRange = Selection.Address
    For Each oSheet In ActiveWindow.SelectedSheets
        With oSheet.PageSetup
            .PrintArea = sPrintRange
        End With
    Next oSheet
End Sub

Reply | Quote

Legare, this is wonderful. My thanks to you and Bill. Since it will work on both multiple AND single sheets, it gets to attached to the regular “SetPrintArea” button.

Reply | Quote

John,

This is a little late, but it works in XL2K. Select all the identical sheets you want to print, then select the cells you want to print on the first sheet. Press Ctrl-P or do File | Print and click the “Print Selection” option button. Print Preview then shows that all the selected sheets will print just that selected area. No need to set a print area at all.

kjk

Reply | Quote