O
owlnevada
In Excel 2007 I keep getting this error when this code encounters a workbook
that has no worksheet objects that match the criteria selected. Most
workbooks have either "abs" or "sum" as part of the tabnames and macro stops
if none exists in many cases. A similar sub to one below will select all the
sheets with "abs" in the tabname. It hangs at "Sheets(Sheetnames).Select".
The errorhandler does not work as entered-is this fixable?
Thanks in advance for any help.
Sub SelectAllSummarys() '(Optional control As IRibbonControl)
'Selects All Summarys Macro Clicks on each summary for further work
' Keyboard Shortcut: Ctrl+Shift+S
Dim i As Integer
Dim Sheetnames() As String
Dim Count As Integer
sumCount = 0
For i = 1 To Sheets.Count
If InStr(Sheets(i).Name, "sum") Then
' got a handle on a summary sheet -
' Save the name of the sheet in an array
Count = Count + 1 ' we need to count the sheets
ReDim Preserve Sheetnames(1 To Count) ' so we can resize the
array to the number of sheets
Sheetnames(Count) = Sheets(i).Name
End If
Next i
' Now all the names of the sheets that we want are in an array, select
them all at once
Sheets(Sheetnames).Select
On Error Resume Next
Exit Sub
End Sub
that has no worksheet objects that match the criteria selected. Most
workbooks have either "abs" or "sum" as part of the tabnames and macro stops
if none exists in many cases. A similar sub to one below will select all the
sheets with "abs" in the tabname. It hangs at "Sheets(Sheetnames).Select".
The errorhandler does not work as entered-is this fixable?
Thanks in advance for any help.
Sub SelectAllSummarys() '(Optional control As IRibbonControl)
'Selects All Summarys Macro Clicks on each summary for further work
' Keyboard Shortcut: Ctrl+Shift+S
Dim i As Integer
Dim Sheetnames() As String
Dim Count As Integer
sumCount = 0
For i = 1 To Sheets.Count
If InStr(Sheets(i).Name, "sum") Then
' got a handle on a summary sheet -
' Save the name of the sheet in an array
Count = Count + 1 ' we need to count the sheets
ReDim Preserve Sheetnames(1 To Count) ' so we can resize the
array to the number of sheets
Sheetnames(Count) = Sheets(i).Name
End If
Next i
' Now all the names of the sheets that we want are in an array, select
them all at once
Sheets(Sheetnames).Select
On Error Resume Next
Exit Sub
End Sub