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

Try this

On Error Resume Next
If UBound(Sheetnames) 0 Then
Sheets(Sheetnames).Select
End If

--
Regards,

OssieMac


"owlnevada" wrote:

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

I have had another look at this and can't believe I missed it.

The On Error should be prior to the code where it is to suppress the error

On Error Resume Next
Sheets(Sheetnames).Select

--
Regards,

OssieMac


"OssieMac" wrote:

Try this

On Error Resume Next
If UBound(Sheetnames) 0 Then
Sheets(Sheetnames).Select
End If

--
Regards,

OssieMac


"owlnevada" wrote:

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

I wouldn't use Count as a variable name.

And I'd allocate enough room in that array for all the sheets (just once) and
then keep track of how many I used. Then resize the array for that number:

Option Explicit
Sub SelectAllSummarys() '(Optional control As IRibbonControl)

Dim i As Long
Dim SheetNames() As String
Dim sCounter As Long

ReDim SheetNames(1 To Sheets.Count)
sCounter = 0
For i = 1 To Sheets.Count
If InStr(1, Sheets(i).Name, "sum", vbTextCompare) Then
sCounter = sCounter + 1
SheetNames(sCounter) = Sheets(i).Name
End If
Next i

If sCounter = 0 Then
MsgBox "No Sum sheets found!"
Else
ReDim Preserve SheetNames(1 To sCounter)
Sheets(SheetNames).Select
End If

End Sub



owlnevada wrote:

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

--

Dave Peterson

That helped with part of the problem. . .

"OssieMac" wrote:

I have had another look at this and can't believe I missed it.

The On Error should be prior to the code where it is to suppress the error

On Error Resume Next
Sheets(Sheetnames).Select

--
Regards,

OssieMac


"OssieMac" wrote:

Try this

On Error Resume Next
If UBound(Sheetnames) 0 Then
Sheets(Sheetnames).Select
End If

--
Regards,

OssieMac


"owlnevada" wrote:

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