Problem with multiple error handling

[DOOGIE]

I wrote a macro to copy data from multiple worksheets on a spreadsheet that I
receive each day. If there is no data to report for one of the worksheets the
worksheet is not included in the spreadsheet that I receive. Therefore I used
multiple On Error statements in my macro (one for each worksheet that may be
missing) to jump to an error handling section for each worksheet to handle
the missing sheet, then jump back into my macro at the section that deals
with the data on the next sheet. The problem that I have is that if more than
one worksheet is missing from the spreadsheet that I receive, then my macro
stops with an error on the second missing worksheet. I have tried inserting
On Error GoTo 0 statements to reset my error handling but that does not help.
What am I doing wrong?

 

[Don Guillett]

Always nice to post your efforts for comments

 

[DOOGIE]

Sorry, below is my macro:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/15/2007 by Douglas L. Eakle
'

'
ChDir _
"\\Shafsv01\data\COMMON\DAILY PERFORMANCE REVIEW MEETING\HOURLY LMP
PRICING"
Workbooks.Open Filename:= _
"\\Shafsv01\data\COMMON\DAILY PERFORMANCE REVIEW MEETING\HOURLY LMP
PRICING\Day Ahead Obligation.xls"
On Error GoTo ErrorHandler1 'Enable error handling for Unit 1 data
Sheets("Harrison 1").Select
Range("A3:C3").Select
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Day Ahead Obligation.xls").Activate
Range("B6:C29").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Unit2:

Windows("Day Ahead Obligation.xls").Activate
On Error GoTo ErrorHandler2 'Enable error handling for Unit 2 data
Sheets("Harrison 2").Select
Range("A3:C3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("G4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Day Ahead Obligation.xls").Activate
Range("B6:C29").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("H7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Unit3:

Windows("Day Ahead Obligation.xls").Activate
On Error GoTo ErrorHandler3 'Enable error handling for Unit 3 data
Sheets("Harrison 3").Select
Range("A3:C3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("L4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Day Ahead Obligation.xls").Activate
Range("B6:C29").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("M7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Lastline:
Windows("Day Ahead Obligation.xls").Activate
ActiveWorkbook.Close SaveChanges:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Exit Sub 'Exit to avoid error handling
ErrorHandler1: 'Error handling routine for Unit 1 data
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("C730").Select
Selection.ClearContents
GoTo Unit2
'
ErrorHandler2: 'Error handling routine for Unit 2 data
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("H7:I30").Select
Selection.ClearContents
GoTo Unit3
'
ErrorHandler3: 'Error handling routine for Unit 3 data
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("M7:N30").Activate
Selection.ClearContents
GoTo Lastline


End Sub

 

[JLGWhiz]

Hi Doogie, You could probably solve the problem by using an If...Then...Else
statement instead of the error handler. ex:

If Not Sheets("Harrison1") is Nothing Then
'Do the copy and paste
Else
'Do the range delete.
End If

That would eliminate extra code for the error handlers also.

 

[DOOGIE]

I added an If...Then...Else statement
If Not Sheets ("Harrison 1") is Nothing Then
'Do the copy and paste
Else
'Do the range delete
End If

However, when my macro gets to the statement "If Not Sheets ("Harrison 1")
is Nothing Then" and there is no sheet "Harrison 1", it returns a runtime
error "9" "subscript out of range" error and stops. What am I doing wrong?