Need some debugging help, please

[slingsh0t]

I have a file that is shared with international users, and have written
code to change paper size from Letter to A4. The current version of
this code enters each sheet individually and changes paper size, but
takes 80 seconds +/- to run.

I've tried to revise it by unhiding sheets, grouping sheets, changing
paper size, then ungrouping sheets as in the code that follows, but
only the "Home" tab, which is the leftmost tab in the workbook, is
affected. The others are still in A4 size.

ActiveWorkbook.Sheets.Select
Sheets("Home").Activate 'tried it with and without this line
With ActiveSheet.PageSetup
.PaperSize = xlPaperLetter
End With

In the course of debugging I recorded a macro to compare my code, and
see that the recorder develops this:
Sheets(Array("Home", "Price & Revenue Control", "Cover Sheet")).Select
....but I can't figure out a way to duplicate that. I *thought* the
ActiveWorkbook.Sheets.Select line would provide that same
functionality, but apparently not.

Any thoughts? Thanks!

 

[Sunny]

Try something like this - I just tested and it ran in a few seconds on
a large workbook and set the paper size for hidden sheets also

Sub SetPaperSize()
For Each mysheet In ActiveWorkbook.Worksheets
mysheet.PageSetup.PaperSize = xlPaperA4
Next
End Sub

Cheers,
S

 

[slingsh0t]

Hi, Sunny-
I appreciate your response! I actually tried that variation earlier
today, and on this particular workbook (13 meg, 31 tabs, many thousands
of calculations) that code takes the 80 or so seconds to run, which I'm
trying to reduce. Can't argue with success, though, that one works.

Thanks again,
Dave O