Select All Worksheets Macro (Excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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”:

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

Reply | Quote