Change print settings across sheets

  • Thread starter Thread starter Alex Burman
  • Start date Start date
A

Alex Burman

Is there a way to change the print settings for all the sheets in a file at
the same time. I seems that when the sheets are selected the print settings
are grayed out.
 
Hi Alex

Depending on your needs you might be able to use Custom Views and Report
Manager.

If of no use you will need VBA code. Record a macro setting up one
sheet. Then go into the VBE and place your recorded code in the macro
below

Sub SetUpAllSheets()
Dim wSheet As Worksheet

For Each wSheet In Worksheets
With wSheet.PageSetup
'YOUR RECORDED CODE HERE
End With
Next wSheet
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Hi Alex!

You cannot set the print area for a group of sheets, see
http://support.microsoft.com/?id=291310 for more information.
But you can record or wite a macro to set the printarea, like:

Sub SetPrintArea()
ActiveSheet.PageSetup.PrintArea = "$A$1:$G$21"
End Sub

Then you can run the macro on the active sheet where you want to set the
print area.


Best regards

Stefan Hägglund
Microsoft
 
This is very easy to accomplish *IF* you're willing to accept *all* the rest
of the PageSetUp options for all the sheets concerned.

By this I mean that you cannot duplicate *only* the print settings, and for
example have a different header.

First, setup one sheet that is to your liking.
Then, hold <Ctrl> and click in the tabs of the rest of the sheets that you
wish to have the same settings (group the sheets).

You must *be sure* that the MAIN sheet ( sheet to be copied *from*) has the
focus, where the tab is in bold.

Then, simply:
<File> <PageSetUp> <OK>.

Now, all the selected sheets have exactly the same Page Set Up settings,
including all the print settings.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Is there a way to change the print settings for all the sheets in a file at
the same time. I seems that when the sheets are selected the print settings
are grayed out.
 
Sorry, this option does not work as the page setup settings are sheet
specific.

The other methods will work, so thanks to those posters.
 
Hi Alex,
Wow, that's tough, only thanks if it works without corrections. <grin>
I know what you mean though, "sorry it didn't work" is good feedback.

BTW, RagDyer's grouping of sheets worked for me (in Excel 2000).
The sheet setup that you want must be selected first into the group. You
have to hit the [OK] button to effect the change. Note use of the word
*focus*. Don't forget to ungroup your sheets when finished or you will
destroy your workbook as any change made to the visible worksheet
will be done to the others. To ungroup right-click on worksheet
tab and choose "Ungroup".

What doesn't work so well is if you try to this with a program, then
you have to cycle through each sheet in the group to make the change to print setup.
 
This *does* work Alex.

If you would care to follow the instructions *exactly*, I'm sure that you'll
find it performs "as advertised".
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Sorry, this option does not work as the page setup settings are sheet
specific.

The other methods will work, so thanks to those posters.
 
Sorry, my mistake. It does work for the main page settings. One area that is
not effected by this procedure is the print range. I should have been more
specific.

Alex
 
Back
Top