Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    VBA code for Selecting All Worksheets (Excel 2002)

    Hi,
    I have the following code to "select all sheets":
    Sheets().Select
    Now I need to "un-select" 4 of the worksheets, as I plan to delete ALL of the worksheets, EXCEPT the four worksheets. I can't seem to get the "deactivate", or "unselect" part of the code.

    Thanks!
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    Excel VBA doesn't support deselecting a worksheet. You can use code like this:

    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
    Case "This", "That", "Some", "Other"
    ' skip
    Case Else
    ws.Delete
    End Select
    Next ws
    Application.DisplayAlerts = True

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    Perfect!
    Thanks Hans!
    Lana

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    Hi,
    Hans helped me with deleting all worksheets except those listed, and now I'm trying to do a "print preview" of the worksheets except those listed, and I can't get it to work correctly. I changed Hans code at the bottom from ws.Delete to ActiveWindow.SelectedSheets.PrintPreview. Any ideas?
    Thanks!
    Lana

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    The code I posted doesn't select the sheets before printing them, because in general, macros work more efficiently if you don't select ranges and sheets.

    For print preview, you could use code like this:

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
    Case "This", "That", "Some", "Other"
    ' skip
    Case Else
    ws.Select Replace:=False
    End Select
    Next ws
    ActiveWindow.SelectedSheets.PrintPreview

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    Hi!
    This works better, however, whichever worksheet I was last working on, which means the worksheet is "selected", this sheet, along with the "Case Else" sheets, is print previewd. For example, if I was working on the worksheet "This", then the worksheet "This" AND all the other worksheets, EXCEPT "That", "Some", "Other" are print previewed. Any ideas?
    Thanks,
    Lana

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    Select one of the other sheets at the beginning of the code: insert a line

    Worksheets("YetAnother").Select

    where YetAnother is an existing sheet other than the four you want to exclude. This line should be above the line "For Each ..."

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    This is a good suggestion, however I'm not allows going to know the name of a worksheet that is NOT in the exception list... this is beacuse before the print preview macro, I run a macro that creates a new worksheet for each person's name in a long list (in this case there are 136 named worksheets). Then this 2nd macro would do a print preview of all 136 reports, however I would not want it to print preview the "This", "That", "Some", "Other" worksheets.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    Is it useful to print preview 132 worksheets? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  10. #10
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    You'd think no, however these are people's individual bonus calculations... there are actually more than 136... my test file only has about 65% of the names. I like to do a quick preview BEFORE we print out all 136 reports... just to spot check a few. The macro that I'd like to do AFTER the print preview macro is a PRINT macro... thought I'd just change the code from ActiveWindow.SelectedSheets.PrintPreview to ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    Try this:

    Dim ws As Worksheet
    Dim f As Boolean
    f = True
    For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
    Case "This", "That", "Some", "Other"
    ' Skip
    Case Else
    ws.Select f
    f = False
    End Select
    Next ws
    ActiveWindow.SelectedSheets.PrintPreview ' or PrintOut

  12. #12
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA code for Selecting All Worksheets (Excel 2002)

    This works awesome!
    Thanks again Hans!
    Lana

Posting Permissions

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