Setting PageSetup Properties in Multiple Worksheets

[Josh Sale]

Right now my VBA code creates a number of worksheets and then sets a bunch
of their PageSetup properties.

Because PageSetup can be so slow, I would like to set all of the properties
at once. I tried:

With Sheets(Array("Sheet2", "Sheet1")).PageSetup
.LeftHeader = "foo"
...
End With

But I get a run-time error on the With statement (438 Object doesn't support
this property or method).

Is there some other way to accomplish this or to dynamically (and
temporarily) define what the default PageSetup properties should be for
newly added worksheets?

TIA,

josh

 

[Jim Cone]

Hello Josh,

Get yourself a test workbook with about 50 or more sheets in it,
select all the sheets and then add some header/footer info.
My experience has been that Excel acts like a snake trying
to swallow a goat when doing this.
It wouldn't be any better using code.

Do one sheet at a time but to reduce the time required...
1. Only change/add those properties you require.
2. Do not leave or have other lines in the code that refer to pagesetup properties.
3. Set displaypagebreaks to false at the start of your code.
4. If you have quite a bit of extra time, look at converting your pagesetup code
to XL4 macro code. It is slightly faster. John Green has a almost
complete example here...
(01/22/2001, "About PageSetup", Excel.programming)
http://makeashorterlink.com/?Q606527BC

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



Right now my VBA code creates a number of worksheets and then sets a bunch
of their PageSetup properties.
Because PageSetup can be so slow, I would like to set all of the properties
at once. I tried:
With Sheets(Array("Sheet2", "Sheet1")).PageSetup
.LeftHeader = "foo"
End With

But I get a run-time error on the With statement (438 Object doesn't support
this property or method).
Is there some other way to accomplish this or to dynamically (and
temporarily) define what the default PageSetup properties should be for
newly added worksheets?
TIA,
josh

 

[Dave Peterson]

How about just looping through the worksheets...

dim wks as worksheet
for each wks in worksheets(array("sheet1","sheet2"))
with wks.pagesetup
...
end with
next wks

 

[Tom Ogilvy]

Set it for one sheet, then copy that sheet instead of adding sheets.

Also lookin xl help for

templates
customizing workbook defaults

also
Create a sheet template for new worksheets


See Sheet.xlt

If you create a single sheet workbook and name it sheet.xlt (after you
format it the way you want), then save it in the xlStart directory, then
insert => WorkSheet

 

[Tom Ogilvy]

for Code, KeepItCool post this method. Format a sheet. Group all the
sheets making it the first sheet in the group. The use sendkeys:

' sheet3 is the pre-formatted sheet
sheets(array("sheet2","sheet3","sheet4")).select
sheets("sheet3").activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show

keepITcool

As I recall, this doesn't do every setting, but most of the more common
ones.

 

[Josh Sale]

Thanks Jim.

I was aware of your suggestions and have already implemented suggestions 1 -
3. I've not taken the step of doing the pagesetup in XL4 but I just might
need to.

Thanks again,

josh

 

[Josh Sale]

Dave,

Basically that's what I have now (actually I do the PageSetup after I add
and format each worksheet). I'm trying to avoid the time it takes to set a
number of PageSetup properties multiplied by the number of worksheets I need
to set them for.

Thanks.

josh

 

[Josh Sale]

Tom,

I considered this approach but couldn't quite figure out how to make it
work.

As I mentioned in my original post, I'm only looking to temporarily make
these PageSetup properties the default for new worksheets. So I guess I
would have to my code:

- Find any existing Sheet.xlt,
- Make a copy of this file,
- Create a new single worksheet workbook with the appropriate PageSetup
properties set,
- Save it as Sheet.xlt in the appropriate place (potentially overwriting the
one that's there),
- Do all of my application processing, and then finally
- Delete the Sheet.xlt I created and put back the original Sheet.xlt
assuming there was one.

Is this what you're purposing? Kind of a lot of work but it might be worth
it.

I'm not even certain it would work. I worry that Excel might open and
process Book.xlt and Sheet.xlt as part of its basic initialization and
therefore wouldn't even "see" my change to Sheet.xlt during the active Excel
session.

Is this what you were thinking or am I off in the weeds.

Thanks again,

josh

 

[Dave Peterson]

You may want to try the suggestion in Tom's post from keepitcool.

 

[Josh Sale]

This is pretty cool ... and it even seems to work!

Is there anyway to keep the File | Page Setup dialog flashing on the screen?

Thanks!

josh

 

[Tom Ogilvy]

I wasn't thinking anything. I was telling you about book.xlt and sheet.xlt.
However, it doesn't read them just one time.

Somehow your list of activities doesn't seem as long as setting up the
formats on every sheet in the workbook.