Results 1 to 9 of 9

Thread: Select all sheets in VBA

  1. #1

    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

    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

    Default

    ActiveWorkbook.Sheets.Select

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700

    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

    Default

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


  6. #6
    New Member
    Join Date
    Jan 2013
    Location
    Jakarta
    Posts
    4

    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

    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

    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

    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

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