| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with multiple error handling
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with multiple error handling
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("C7:D30").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 "Don Guillett" wrote: Always nice to post your efforts for comments -- Don Guillett Microsoft MVP Excel SalesAid Software "DOOGIE" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with multiple error handling
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" wrote: 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("C7:D30").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 "Don Guillett" wrote: Always nice to post your efforts for comments -- Don Guillett Microsoft MVP Excel SalesAid Software "DOOGIE" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with multiple error handling
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? "JLGWhiz" wrote: 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" wrote: 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("C7:D30").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 "Don Guillett" wrote: Always nice to post your efforts for comments -- Don Guillett Microsoft MVP Excel SalesAid Software "DOOGIE" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error handling problem | Excel Discussion (Misc queries) | |||
| Problem with handling on error when file is already open | Excel Programming | |||
| Error Handling problem | Excel Programming | |||
| Error Handling Problem | Excel Programming | |||
| Excel VBA - Error handling problem | Excel Programming | |||
Linear Mode
