Select all sheets in VBA

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Select all sheets in VBA

  1. #1
    Board Regular oliver_penney's Avatar
    Join Date
    Dec 2002
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    there is probably a one line answer to this, but how do i select all (visible) sheets?

    if a record a macro it creates an array of the worksheet names, which is no good if the names change

    thanx in advance

    ol

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Welcome to the Board!


    Sheets.Select


    that should do it.

    EDIT:: Should've tested that with some hidden sheets really shouldn't I? Nate Oliver's solution is the way to go.
    _________________
    Mark O'Brien

    Columbus Ohio Celtic Supporters Club

    [ This Message was edited by: Mark O'Brien on 2002-12-19 10:48 ]

  3. #3
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ActiveWorkbook.Sheets.Select

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Howdy Oliver (sharp name!), welcome to the board. How's about the following:


    Sub gram_em()
    Dim ws As Worksheet
    For Each ws In Sheets
    If ws.Visible Then ws.Select (False)
    Next
    End Sub



  5. #5
    Board Regular oliver_penney's Avatar
    Join Date
    Dec 2002
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ta Nate it was that ****ing false thing i'd missed


  6. #6
    New Member
    Join Date
    Jan 2013
    Location
    Jakarta
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select all sheets in VBA

    Hi Nate,

    What the use for "(false)" in that syntax?
    If i using that syntax, error message appear

    "run time error '1004'
    application-defined or object-defined error"

    But when i delete the (false), all the syntax work like charm

  7. #7
    Board Regular
    Join Date
    Jan 2013
    Location
    Columbus, Ohio, United States of America
    Posts
    618
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select all sheets in VBA

    It was 2002 when Nate wrote that. I don't think it likely that he is still subscribed to this thread.

  8. #8
    New Member
    Join Date
    Jun 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select all sheets in VBA

    Quote Originally Posted by Badai View Post
    Hi Nate,

    What the use for "(false)" in that syntax?
    If i using that syntax, error message appear

    "run time error '1004'
    application-defined or object-defined error"

    But when i delete the (false), all the syntax work like charm

    I believe the (false) flag tells Excel not to replace whatever's currently selected with the object you're selecting (I think it only works with worksheets; not sure it works with ranges, etc.). (true) would tell it to select the sheet as the only sheet selected; (false) tells it to keep selecting.

  9. #9
    New Member
    Join Date
    Apr 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select all sheets in VBA

      
    I know it is an old thread, but it was usefully to me....
    Remove the brackets () around FALSE

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