Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: macro - Select All Sheets

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to create a macro to select all sheets in active workbook and copy them into a new book (Named "Copy.xls") that doesn't contain the macros and buttons in the original used to launch the macro. Any quick way to do this?

    At the least, I would like to know how to write code to select all sheets. THANKS!

    [ This Message was edited by: Cosmos75 on 2002-04-08 12:03 ]

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Worksheets.copy will copy all the worksheets to a new workbook. As for not copying code, I do not know.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub Select_All_Sheets()
    Dim sht As Worksheet
    Dim SelectMe() As String
    Dim s As Integer
    For Each sht In Worksheets
    s = s + 1
    ReDim Preserve SelectMe(1 To s) As String
    SelectMe(s) = sht.Name
    Sheets(SelectMe).Select
    Next
    End Sub

    It's never too late to learn something new.

    Ricky

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Got this code to select buttons and delete them.

    ActiveSheet.Shapes.Range(Array("Button 1", "Button 2", "Button 3")).Select

    Is there a way to get it to select all the buttons and delete them?

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about the following:

    ActiveSheet.Shapes.SelectAll
    Selection.Delete
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following code will accomplish the same as the code I posted above.

    worksheets.Select
    It's never too late to learn something new.

    Ricky

Some videos you may like

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
  •