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

    Select All Worksheets Macro (Excel 2002)

    Hello,
    I've recorded the macro below in Excel, however I'd like to eliminate the actual names of the worksheets and just have the macro select ALL of the worksheets, EXCEPT the worksheet called "data" and the worksheet called "report". Can someone please tweek this code for me if it's possible.
    Thanks!
    Lana

    Sub ShowPagesFormat()
    '
    ' ShowPagesFormat Macro
    ' Macro recorded 10/5/2006 by Lana McCoy
    '

    '
    Range("A6").Select
    ActiveSheet.PivotTables("PivotTable1").ShowPages PageField:="Vendor Name"
    Sheets(Array("TEST1", "TEST2", "TEST3", "TEST4", "TEST5", "TEST6", "TEST7", "Report", "Data")).Select
    Sheets("CARGILL MEAT SOLUTIONS CO").Activate
    Cells.Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets(Array("TEST1", "TEST2", "TEST3", "TEST4", "TEST5", "TEST6", "TEST7")).Select
    Sheets("CARGILL MEAT SOLUTIONS CO").Activate
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Cells.Select
    Cells.EntireColumn.AutoFit
    Selection.ColumnWidth = 9
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("B:B").Select
    Selection.ColumnWidth = 8.22
    Range("A1").Select
    ActiveWindow.SmallScroll Down:=-15
    Rows("1:3").Select
    Selection.Insert Shift:=xlDown
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("QA Report").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("CARGILL MEAT SOLUTIONS CO").Select
    Rows("2:3").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    End Sub

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

    Re: Select All Worksheets Macro (Excel 2002)

    You can use the following code to select all worksheets except data and report:

    Dim wsh As Worksheet
    Dim f As Boolean
    f = True
    For Each wsh In Worksheets
    Select Case wsh.Name
    Case "data", "report"
    ' don't select
    Case Else
    ' select
    wsh.Select Replace:=f
    f = False
    End Select
    Next wsh

    I use the boolean variable f to replace the current selection with the first appropriate sheet (f = True initially), then add subsequent sheets to the selection (f = False after the first sheet has been selected).

    Note: your code contains some repetitions (this often happens when recording a macro). For example:

    Cells.Select
    Cells.EntireColumn.AutoFit
    Selection.ColumnWidth = 9
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("B:B").Select
    Selection.ColumnWidth = 8.22

    This can be reduced to

    Cells.EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 8.22

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select All Worksheets Macro (Excel 2002)

    Before we edit your routine, what do you want to do to all of the selected Worksheets? It is often advantageous not to select worksheets (or cells, rows, etc.).

    The following routine identifies all of the worksheets in the active workbook that are not named "data":

    <pre>Public Sub FindMySheets()
    On Error goto Err_FindMySheets

    Dim i As Integer, iIndex As Integer
    Dim sWorksheets() As String

    With Application.ActiveWorkbook
    For i = 1 To .Worksheets.Count
    If .Sheets(i).Name <> "data" Then
    iIndex = iIndex + 1
    ReDim Preserve sWorksheets(iIndex)
    End If
    Next i
    End With

    '**** Do something here with the array

    Exit_FindMySheets:
    Exit Sub

    Err_FindMySheets:
    Call ErrHandler()
    Resume Exit_FindMySheets

    End Sub
    </pre>


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

    Re: Select All Worksheets Macro (Excel 2002)

    Hi Hans,
    Thanks for the help Hans! I've consolidated the "select all worksheets" macro with another macro that saves each worksheet as its own file (please see below), however I've tried to adopt your case, case else concept for those "report" and "data" worksheets and I've blown it. Can you tweek the save worksheets part of the macro to save all the different worksheet names EXCEPT the "report" and the "data" worksheets?
    Thanks!
    Lana


    Sub SelectAllWorksheetsFormatSave()

    'Show pages to create a new worksheet for every vendor name
    ActiveSheet.PivotTables("PivotTable1").ShowPages PageField:="Vendor Name"

    'Select all worksheets except the 3 worksheets listed below
    Dim wsh As Worksheet
    Dim f As Boolean
    f = True

    For Each wsh In Worksheets
    Select Case wsh.Name
    Case "Data", "Report", "date vlookup"
    ' don't select
    Case Else
    ' select
    wsh.Select Replace:=f
    f = False
    End Select
    Next wsh

    'Save each worksheet as its own file
    Dim ShHome As String
    ShHome = ActiveSheet.Name

    For Each wsh In Worksheets
    ActiveWorkbook.SaveAs (ActiveSheet.Name & ".xls")
    ActiveWorkbook.Close
    Case "Data", "Report", "date vlookup"
    Case Else
    wsh.Select Replace:=f
    Next wsh

    End Sub

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

    Re: Select All Worksheets Macro (Excel 2002)

    To save individual sheets, there is no need to select them all. Here is a macro that will save each sheet to a separate file except for those specified explicitly:

    Sub SelectAllWorksheetsFormatSave()
    Dim wsh As Worksheet
    'Show pages to create a new worksheet for every vendor name
    ActiveSheet.PivotTables("PivotTable1").ShowPages PageField:="Vendor Name"

    For Each wsh In Worksheets
    Select Case wsh.Name
    Case "Data", "Report", "date vlookup"
    ' don't do anything
    Case Else
    ' copy sheet to new workbook
    wsh.Copy
    With ActiveWorkbook
    ' save it
    .SaveAs Filename:=ActiveSheet.Name & ".xls"
    ' and close it
    .Close SaveChanges:=False
    End With
    End Select
    Next wsh
    End Sub

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

    Re: Select All Worksheets Macro (Excel 2002)

    That of course works great Hans... thank you!! The reason I was wanting to select ALL the worksheets (except the "data" & "report" worksheets) was because I wanted to format every worksheet the same (add titles, fonts, bolds, set print area, etc.) and I thought if I selected all of them (except the two) then it would format them all at once, HOWEVER, this method doesn't work when it comes to the "set print area" function... so I'm scrapping the select all the worksheets route, and going with just wanting to format EVERY worksheet (except the two mentioned). Attached is my recorded macro and the help you've given me with the "select all" and the "save each worksheet". As you'd expect it's not working because I'm need to apply that "except" concept to the "data" & "report" worksheets when it comes to the formatting... can you help me with this please. Oh, and I'd like to add the date (located in cell a1 on the "report" worksheet) to the beginning of the name of each worksheet that is saved as a file by itself in the last step of the macro you wrote. Is this possible?
    Thanks as always Hans!!
    Lana

    Long piece of code moved to attachment by HansV

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

    Re: Select All Worksheets Macro (Excel 2002)

    The first loop processes all worksheets except 'data' and 'report', and the second one all worksheets except 'data', 'report' and 'date vlookup'. Is that what you intended? If not, what did you intend?

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

    Re: Select All Worksheets Macro (Excel 2002)

    Hi Hans... I had that screwed up... it should just be "Data", "Report" for both loops. I've fixed that part, but now the formatting is not unselecting the sheet when it moves on to the next worksheet, therefore causing the formatting to happen every time on the first sheet (9 times), 8 times on the 2nd sheet, 7 times on the 3rd sheet, etc... any ideas?
    Thanks,
    Lana

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

    Re: Select All Worksheets Macro (Excel 2002)

    That's because you left some of the code that was intended to select all sheets (except for ...). In the attached text file, I have combined the two loops into one. Each sheet is saved at the end of the loop. The date from Report!A1 is prepended to the filename in yyyymmdd format to make it easy to sort the files in Explorer. You can modify this if you like, but please note that you cannot use / in a file name, so mm/dd/yyyy is not allowed. As you will see, the code doesn't select cells, it applies formatting etc. to ranges instead of to the selection.

    Note: the macro will probably run slowly. This is a problem with Excel - changing the page setup from VBA is very slow.

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

    Re: Select All Worksheets Macro (Excel 2002)

    Hi Hans,
    Everything is working now except my date... I keep getting a "subscript out of range" error... any ideas?
    Thanks,
    Lana

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

    Re: Select All Worksheets Macro (Excel 2002)

    Oops, that's my fault. I didn't look closely enough at the code. I referred to the Report sheet at a moment when a new single-worksheet workbook was active. Try the attached modified version. It sets a string variable at the beginning of the code.

Posting Permissions

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