Page Setup for All Worksheets 1

[NMiller007]

I am using an Access macro to export data to multiple Excel worksheets. I have all of the data going to Excel and most of the formatting working.

Now I need to set the Page Setup options for all of these worksheets. I am zooming to 90%, adding a footer, changing margins, and repeating rows at the top. Here is the code I am using:

.Sheets.Select
With .ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$14"
    .CenterHeader = ""
    .CenterFooter = "Page &P of &N"
    .LeftMargin = 36
    .RightMargin = 36
    .TopMargin = 36
    .BottomMargin = 36
    .CenterHorizontally = True
    .Zoom = 90
End With

This is only doing the page setup for the first worksheet in the list. Am I missing something?

I tried doing the page setup portion for each worksheet as I was creating it, but that seemed really slow and somehow the macro finished without even starting to format the last few worksheets (maybe a memory issue?). I also tried using a Sheet Array instead of .Sheets.Select and that only setup the first sheet as well.

Thanks!

 

[SkipVought]

Select ALL sheet. Then do the setyp.

Skip,

_[red][/red]

 

[SkipVought]

...or run the same setup on all sheets.

Skip,

_[red][/red]

 

[NMiller007]

I tried that. I used:

.Sheets(Array("130983", "210232", "222574", "240213", "240219", "240223", "240376", _
    "240462", "240463", "240464", "240465", "250004", "360003", "360009", "480004", _
    "500250", "500637", "520255", "520268", "550222", "550225")).Select
.Sheets("130983").Activate

before the With statement, which happens to be all the sheets that run this time. That also only formatted the first sheet. Also, this wouldn't work directly because the names of the sheets and number of sheets would change each time. I created a string with all of the sheets in it and used that in the array, but that only got the first page as well.

I also tried the page settings for each page as I was creating it, but

that seemed really slow and somehow the macro finished without even starting to format the last few worksheets (maybe a memory issue?).

That method did get more than the first page though. The last few worksheets only had what was outputted by the transferspreadsheet and din't get any of the formatting or the page setup.


I tried .Sheets.Select to select all sheets, but that only setup the first page as well.

 

[AnotherHiggins]

This is a known problem with Excel VBA.

See this Microsoft Knowledge Base article for more info:

http://support.microsoft.com/kb/124934#appliesto

The workaround listed on that page will be needed:

 Sub ChangePageSetup()
      ' Dimension variable x as Object type
      Dim x As Object
      ' Group select Sheet1, Sheet2, and Sheet3 worksheets
      ' Note that there are other methods for selecting multiple sheets
      Sheets(Array("sheet1", "sheet2", "sheet3")).Select

      ' Change page setup options for sheet
      For Each x In ActiveWindow.SelectedSheets
         With ActiveSheet.PageSetup
            .LeftMargin = Application.InchesToPoints(2)
            .RightMargin = Application.InchesToPoints(2)
            .Orientation = xlLandscape
         End With
      Next x
   End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Grr. The link should just be

http://support.microsoft.com/kb/124934.

The link I listed before takes you to the bottom of the same page. Even though the page says this applies to Excel 95, I know it wasn't fixed up through 2000.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[NMiller007]

Thanks John. What a pain! I saw the ActiveSheet part, but I just figured that Excel didn't care that it wasn't plural (kind of like Sheets(1).Select).

I am using Excel 2003 SP2 and I am still having that problem. Maybe Excel 2007 has it fixed?

Using a loop to do the page setup at the end gets all of my pages and I don't have other issues, but it is SLOW!

Thanks for the help!

 

[AnotherHiggins]

Make sure to disable screen updating. That will speed up your code:

Application.ScreenUpdating = False
[tab]'your code here
Application.ScreenUpdating = True

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SwingFox]

Hell anotherhiggins,

Do you happen to have more tricks than setting screenupdating to false? I experience the same slow code, even though I did set screenupdating to false.

Thanks!
- SwingFox

 

[AnotherHiggins]

Hey, SwingFox.

Sure. [ul]
[li]Turn off ScreenUpdating[/li]
[li]Avoid loops whenever possible[/li]
[li]Avoid use of Select and Activate[/li]
[li]Take advantage of opportunities to use With - End With[/li]
[/ul]

Also see Skip's faq707-4105.

And we just recently had a related discussion in thread707-1407494. In that thread, see firefytr's suggestion to use a called procedure to disable events in his post dated 16 Sep 07 14:44.

Good luck!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlennUK]

Hi SwingFox,

those are good hints from AnotherHiggins, or were you specifically wanting hints for speeding up Page Setup? If so, I've tried doing the Page Setup through the Excel 4 macro command and it's a lot faster.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.