VBA Select All But Four Sheets In Workbook

Hi, hobbiton73,

any Select you perform will only have one sheet to refer to, the last sheet you found. Maybe try it like this building an array and using that for selecting (although any Selection should be useless as action may be done withour this):

Sub SelectSheets()
Dim sh As Worksheet
Dim strArr()
Dim lngArray As Long

lngArray = 1
For Each sh In ActiveWorkbook.Worksheets
  Select Case sh.Name
    Case "Macros", "All Data", "Flexible Resources List", "Unique Records"
    Case Else
      ReDim Preserve strArr(1 To lngArray)
      strArr(lngArray) = sh.Name
      lngArray = lngArray + 1
  End Select
Next sh
Sheets(strArr).Select
End Sub

Ciao,
Holger

Hi, hobbiton73,

I´m a bit out of time by now - so it´s sort of "Typos To Go":

Sub SelectSheets()
Dim sh As Worksheet     'variable for worksheets
Dim strArr()            'array to be used to hold the sheet names
Dim lngArray As Long    'variable used as upper limit for teh array

lngArray = 1            'starting value for the array
For Each sh In ActiveWorkbook.Worksheets
  'check the worksheet name
  Select Case sh.Name
    Case "Macros", "All Data", "Flexible Resources List", "Unique Records"
      'these are the names not to select
    Case Else
      'code for all other sheets to work with
      'preserve means hold the values inside the array, redim means give a new upper bound for the array
      'we set the upper bound starting from 1 using our variable
      ReDim Preserve strArr(1 To lngArray)
      'put the name of the worksheet into the array as the last value in the array / upper bound
      strArr(lngArray) = sh.Name
      'add 1 to the variable for a possible next sheet to add
      lngArray = lngArray + 1
  End Select
Next sh
'we're through, just select all worksheets using the array we hold
'you may run into an error if you only have sheets in the workbook that include names of sheets not to select
Sheets(strArr).Select
End Sub

Ciao,
Holger