Error Trapping (Excel 97)

Topic

New Reply

Wasn’t sure what the subject of this thread should have been!

I have the following code:

Dim ws As Worksheet
For Each ws In Worksheets(Array(“Smith Pipe”, “Smith Fcst”, “Cole Pipe”, “Cole Fcst”, “Flock Pipe”, “Flock Fcst”))
ws.Activate
With ActiveSheet

End With
Next ws

The worksheets are created from another set of code based on the data in my spreadsheet. The problem is that sometimes I do not have all 6 of those spreadsheets. Sometimes I will only have “Smith Pipe”, “Flock Fcst” & “Cole Pipe” in my workbook.

So what occurs is that I get an error when it goes to the worksheets that I have named in the above code.

Is there someway to say – If worksheet does not exist go on to next one in array?

Thanks!

Reply | Quote

Replies

Hi awckie,

You could use:
On Error Resume Next
before the For … line, so that the routine skips over any missing sheets, but that won’t help if a user has re-named the sheet. So you might need to re-think the approach by using a routine that goes though all sheets and tests for an attribute (perhaps in a protected cell). Some code I got for doing something along those lines (possibly from another posting here in the Lounge) is:

Sub MultiSheetsSelect()
Dim SheetArray() As Variant
Dim ws As Worksheet
Dim indx As Integer
For Each ws In ThisWorkbook.Sheets
If ws.Range(“A1”) = “x” Then
ReDim Preserve SheetArray(indx)
SheetArray(indx) = ws.Index
indx = indx + 1
End If
Next
If indx > 0 Then
Sheets(SheetArray()).Select
End If
End Sub

The above example selects all sheets with an “x” in cell A1.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

The things you learn everyday

I added the On Error Resume Next before the For Line……

Sub TBCO_FormatHCDetailSheets()

Application.StatusBar = “Creating Head Coach Detail”

Sheets(“Smith Pipe”).Select

Dim ws As Worksheet
On Error Resume Next
For Each ws In Worksheets(Array(“Smith Pipe”, “Smith Fcst”, “Cole Pipe”, “Cole Fcst”, “Flock Pipe”, “Flock Fcst”))
ws.Activate
With ActiveSheet

End With
Next ws

End Sub

What is happening now is that I have the following worksheets on this batch – Smith Fcst, Smith Pipe, Flock Fcst, Flock Pipe. What happens is that it performs all the code on the Smith Fcst worksheet, but then completely ends the subroutine and moves on to the next subroutine. So it’s not really moving on to the next worksheet.

What do you think is happening?

(I don’t have to worry about the user changing the name of the worksheets, as the worksheets are created automatically right before this sub is performed with no available user intervention – Well I’m sure there probably is a way for user intervention, but believe me – my 2 coworkers who are using this will just yell for me if there is an issue

Reply | Quote

Is the sequence important? If not, you could loop through all the Sheets (as shown in Macropod’s code) and check whether the current sheet is on the list, e.g.,

If InStr(1, “|Smith Pipe|Smith Fcst|Cole Pipe|Cole Fcst|Flock Pipe|Flock Fcst|”, “|” & ws.Name & “|”) > 0 Then

Please note that I just typed that out there, and didn’t check that ws.Name actually is the correct property, but you get the idea. Also, you might need to set the compare type to Text (otherwise it is case-sensitive).

Reply | Quote

On Error Resume Next skips the entire line if any part of the line is invalid. A more tedious way to handle this is:

Dim ws As Worksheet
Dim wsArr As Variant
Dim intC As Integer
wsArr = Array(“Smith Pipe”, “Smith Fcst”, “Cole Pipe”, “Cole Fcst”, “Flock Pipe”, “Flock Fcst”)
For intC = 0 To UBound(wsArr)
On Error Resume Next
Set ws = Worksheets(wsArr(intC))
If Not ws Is Nothing Then
ws.Activate
With ActiveSheet
‘
End With
End If
Set ws = Nothing
Next intC

I’ll have to check into JScher2000’s approach, it’s interesting.

BTW, are you sure you need to Activate the ‘ws’? There are very few times Activate is needed; some print settings and View setting of the ws require it, but otherwise you can run code on the sheet without Activating it, and it speeds things up a bit. Try just using:

If Not ws Is Nothing Then
With ws
‘
End With

Reply | Quote

Note that you needn’t activate the sheet to work on it:

WS.activate
With Activesheet

can be replaced with:

With WS

It also speeds up your code.

Reply | Quote