Speed up VBA multi-sheet Page Setup settings? (200

Topic

New Reply

Hi,

Fairly new to Excel macros, so I may just be missing something obvious here but —

I’m writing a macro that creates a number of new data sheets in a workbook and needs to set each up with identical Page Setup settings. I can select all these sheets, but have not managed to figure out a way to then apply the Page Setup settings all at once. I imagine I can do it with sendkeys, but is there an object I can specify that will do this?

And a second question – I selected all the new sheets with an array variable containing all their names. Then I ran across this code to selectively select sheets in a book:

For SheetNum = 1 to Wkbook.worksheet.count
If Left(Wkbook.worksheets(SheetNum).name,3) = “LST” then Wkbook.Worksheets(SheetNum).Select False
Next SheetNum

I’m not familiar with the construction “Wkbook.Worksheets(SheetNum).Select False”
How is that operating to select the sheet in question?

Thanks very much

Reply | Quote

Replies

You can’t change the page setup for multiple sheets at once in a macro. You’ll have to loop through the sheets. Or set up one sheet correctly, then copy it.

If you click in the word Select in the Visual Basic Editor and press F1, you’ll get online help. You’ll see that the Select method for (among others) the Worksheet object has an optional argument Replace. If you specify True or omit this argument, the new selection replaces the previous one. If you specify False, the new selection is added to the existing one.
So in the loop you mention, each worksheet whose name starts with “LST” is added to the group of selected worksheets.

Reply | Quote

Thanks, Hans, for both explanations. I think I will use your idea – create one sheet with the settings and then copy it.
Regards

Reply | Quote