Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Speed up VBA multi-sheet Page Setup settings?

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •