Page set-up when selecting multiple sheets

  • Thread starter Thread starter Jean-Marc
  • Start date Start date
J

Jean-Marc

Hello there,

Having problems when writing macro and wishing to set-up a
number of sheets for print in the same format.

The following statement only returns the appropriate set-
up for the first sheet, in the example below, the
Finalised Sheet.

Sheets(Array
("Finalised", "Swanley", "Plant", "STRTU")).Select
With ActiveSheet.PageSetup


Have I discovered a bug or is there a fix ?
Thanks in advance,
Jean-Marc
 
Unfortunately, there is no good way to do a multiple PageSetup using VBA
without using a loop, unless you want to use the dreaded SendKeys to
simulate keystrokes.
 
Jean-Marc,

You'll have to use the ActiveWindow property of the Excel.Application class to iterate through the multiple sheet selection (effectively a "group edit") you've set up. Here's how you might do that:

dim wks as Worksheet

Sheets(Array
("Finalised", "Swanley", "Plant", "STRTU")).Select

for each wks in ActiveWindow.SelectedSheets
with wks.PageSetup
 
Back
Top