Results 1 to 8 of 8
Like Tree1Likes
  • 1 Post By NateO

Select all sheets in VBA

This is a discussion on Select all sheets in VBA within the Excel Questions forums, part of the Question Forums category; there is probably a one line answer to this, but how do i select all (visible) sheets? if a record ...

  1. #1
    Board Regular oliver_penney's Avatar
    Join Date
    Dec 2002
    Posts
    182

    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,702

    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


    cheekbones3 likes this.

  5. #5
    Board Regular oliver_penney's Avatar
    Join Date
    Dec 2002
    Posts
    182

    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
    617

    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

    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.

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