Justinlabenne
01-07-2005, 11:12 PM
I need to provide some error handling for a file I developed at work. This file opens multiple workbooks with varying names, the workbooks are hours updated by 11 seperate people. Trouble is they keep renaming or moving their files every now and again. I had a basic "On error goto" but the user of my file would like to know which file was moved so he can pinpoint who dunnit. I cannot seem to get multiple error handlers to work correctly, and it seems like the long way round also. Heres is some code sampling below. Any ideas are welcome and more than grateful. Thanks
On Error GoTo Err_Mold
M1 = "K:\AIC Public\EXPERT OJT\Blow Mold OJT\MOLDIN OJT MATRIX'S\Training Hours (All Shifts).xls"
Workbooks.Open Filename:=M1
Range("One").Copy
ActiveWorkbook.Close
Sheets("Mold").Select 'Select sheet for dept
Range("B16").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
PostOp1:
On Error GoTo Err_PostOp
P1 = "K:\AIC Public\EXPERT OJT\Post Op OJT\1st Shift\HERB' MATRIX 1ST SHIFT.xls"
Workbooks.Open Filename:=P1
Range("Me").Copy
ActiveWorkbook.Close
Sheets("PostOp").Select 'Select sheet for dept
Range("B16").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Error handlers look like this:
Err_Mold:
MsgBox "The filename below has been changed, moved, etc.. the information cannot be found " & vbCr & vbCr & M1
Err.Clear
GoTo PostOp1:
I am sure there is a better way I just cant seem to get to it.
On Error GoTo Err_Mold
M1 = "K:\AIC Public\EXPERT OJT\Blow Mold OJT\MOLDIN OJT MATRIX'S\Training Hours (All Shifts).xls"
Workbooks.Open Filename:=M1
Range("One").Copy
ActiveWorkbook.Close
Sheets("Mold").Select 'Select sheet for dept
Range("B16").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
PostOp1:
On Error GoTo Err_PostOp
P1 = "K:\AIC Public\EXPERT OJT\Post Op OJT\1st Shift\HERB' MATRIX 1ST SHIFT.xls"
Workbooks.Open Filename:=P1
Range("Me").Copy
ActiveWorkbook.Close
Sheets("PostOp").Select 'Select sheet for dept
Range("B16").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Error handlers look like this:
Err_Mold:
MsgBox "The filename below has been changed, moved, etc.. the information cannot be found " & vbCr & vbCr & M1
Err.Clear
GoTo PostOp1:
I am sure there is a better way I just cant seem to get to it.