nice easy one...how do you select multiple worksheets in a m
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: nice easy one...how do you select multiple worksheets in a m

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I know how to select multiple cells and multiple worksheets, but how do you select a range of worksheets? My worksheets always end up in the proper order with the same sheet to start and to end, but various sheets in between. Can I select the first through last so I can assign properties easier? My primary need is to easily set the page setup to one x one pages for each sheet and then print. Thanks.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Pardon! sorry I'm not sure what you're asking. Bound to be me though.
    can you not record what you want to achieve and amend the code?
    "Have a good time......all the time"
    Ian Mac

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The Example is Sheets named (in order)
    StartSheet
    Sheet2
    Sheet3
    Sheet4
    Sheet5
    EndSheet

    Start and End are always there, but the amound of sheets (and their names) changes each time. I would like to be able to select StartSheet THROUGH EndSheet. I tried recording the selection, but ended up with an array with each sheet's name. That's what I'm trying to avoid as I won't know each sheet's name. Thanks for the help.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following will select all the sheets in a workbook, hth: -

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    ws.Select (False)
    Next ws

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's a question to develop a work-around: How can I find out what number corresponds to the order of a sheet? For example: If the StartSheet is always Sheet(2), is there a method and/or property that will return the 2? If so, I could get the number of the first and last sheets and use a simple loop to select everything in between.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I thought you just wanted all the sheets selected. If you want all the sheets, apart from the start and end sheets selected, then run this: -

    Dim ws As Worksheet

    Sheets(2).Select

    For Each ws In ActiveWorkbook.Worksheets
    If ws.Index <> 1 And ws.Index <> Worksheets.Count Then
    ws.Select (False)
    End If
    Next ws

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ".Index": That's what I was looking for!!

    Thanks Mudface.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dim ws as Worksheet
    ...
    ws.Select (False)
    ...


    What is the "(False)" for?

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    From VBA help (basically it allows you to select several sheets in one go, rather than having to use the array that you get from recording the macro):-


    expression.Select(Replace)

    expression Required. An expression that returns an object in the Applies To list.

    Replace Optional Variant (used only with sheets). True to replace the current selection with the specified object. False to extend the current selection to include any previously selected objects and the specified object.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com