setting page setup/print area for multiple sheets

[n***@sneakemail.com]

I have an excel file with many sheets. I would like to do the same
'Page Setup' and 'Print Area ->Set Print Area' for every page in the
file. Is there a way to do this without having to do each sheet
manually?

Thanks.

[Stephen]

Post by n***@sneakemail.com
I have an excel file with many sheets. I would like to do the same
'Page Setup' and 'Print Area ->Set Print Area' for every page in the
file. Is there a way to do this without having to do each sheet
manually?
Thanks.

Select all the sheets. (To do this, click on the tab of the first, then hold
down SHIFT whilst clicking the tab of the last). Then, whatever you do to
the visible sheet (such as setting the print area) will be applied to all.
So, don't forget to remove the multiple sheet selection (click any
intermediate sheet tab) before you carry on with other work!

[Ken Johnson]

Post by Stephen

Post by n***@sneakemail.com
I have an excel file with many sheets. I would like to do the same
'Page Setup' and 'Print Area ->Set Print Area' for every page in the
file. Is there a way to do this without having to do each sheet
manually?
Thanks.

Select all the sheets. (To do this, click on the tab of the first, then hold
down SHIFT whilst clicking the tab of the last). Then, whatever you do to
the visible sheet (such as setting the print area) will be applied to all.
So, don't forget to remove the multiple sheet selection (click any
intermediate sheet tab) before you carry on with other work!

Stephen,

When I tried that (xl2003) the Set Print Area option was greyed out.

I think a macro needs to be used...

Option Explicit
Sub PrintAreaAllWkshts()
Dim strPA As String, Sht As Worksheet
On Error GoTo NOT_RANGE
strPA = Selection.Address
For Each Sht In ActiveWorkbook.Worksheets
Sht.PageSetup.PrintArea = strPA
Next
Exit Sub
NOT_RANGE: MsgBox "Select the Print Area Cells, then try again!"
End Sub

seems to work.

Ken Johnson

[Don Guillett]

Or
for each ws in worksheets
range("a1:b6").printout
next
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

Post by n***@sneakemail.com
I have an excel file with many sheets. I would like to do the same
'Page Setup' and 'Print Area ->Set Print Area' for every page in the
file. Is there a way to do this without having to do each sheet
manually?
Thanks.

[n***@sneakemail.com]

Post by Don Guillett
Or
for each ws in worksheets
range("a1:b6").printout
next
--

I tried this. Unfortunately, this caused the range of the current
sheet to be printed the number of times as there were sheets.

How do I indicate the range for the current ws when iterating through
the list?

-Eric

[Don Guillett]

My booboo
for each ws in worksheets
ws.range("a1:b6").printout
next

If the range changes with each ws you will need to use something else such
as select case. What are the details?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

Post by n***@sneakemail.com

Post by Don Guillett
Or
for each ws in worksheets
range("a1:b6").printout
next
--

I tried this. Unfortunately, this caused the range of the current
sheet to be printed the number of times as there were sheets.
How do I indicate the range for the current ws when iterating through
the list?
-Eric

[Gord Dibben]

Using VBA code you can set the same print range and rows to repeat at top on
each sheet in grouped sheets.

For anyone interested in the code see this google search result with Dave
Peterson and myself posting. The print area code is from Bob Flanagan.

http://snipurl.com/106kz


Gord Dibben MS Excel MVP

Post by n***@sneakemail.com
I have an excel file with many sheets. I would like to do the same
'Page Setup' and 'Print Area ->Set Print Area' for every page in the
file. Is there a way to do this without having to do each sheet
manually?
Thanks.