Error Handling driving me crazy. Please help
 

This is driving me mad. Why am I getting this second error? I a
combining two workbooks (and doing many other things to them) copyin
tables sheet by sheet. Once the sheets in the workbook run out, a
error is given. To get around this, I added the On Error GoT
ScanTrack step. Then it steps through the second document sheet b
sheet until the error: Run-time error '9' Subscript out of range
stops the code. I have On Error GoTo ErrRequal. Shouldn't this b
enough to step it out of the loop without stopping the code? How do
get past this error?



Do
With Workbooks("Package Tracking3.xls").Worksheets(i)
Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row
15)).Copy
Cells(65536, 1).End(xlUp)(2).Select
ActiveSheet.Paste
End With
i = i + 1
On Error GoTo ScanTrack
Loop
ScanTrack:
i = 1
Do
With Workbooks("Scan Tracking3.xls").Worksheets(i)
Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row
15)).Copy
Cells(65536, 1).End(xlUp)(2).Select
ActiveSheet.Paste
End With
i = i + 1
On Error GoTo Format
Loop
Format

--
hyyft
-----------------------------------------------------------------------
hyyfte's Profile: http://www.excelforum.com/member.php...fo&userid=1318
View this thread: http://www.excelforum.com/showthread.php?threadid=26246

Error Handling driving me crazy. Please help
 

Hi hyyfte,

I'm not clear on why you're using a do loop when you know how many
worksheets are there. Why not something like:

For i = 1 to Workbooks("Package Tracking3.xls").Worksheets.Count
''' The rest of your code here
Next i

For i = 1 to Workbooks("Scan Tracking3.xls").Worksheets.Count
''' The rest of your code here
Next i

This is a cleaner solution to the task that will plug directly into your
existing code and eliminate the use of erros for flow control entirely.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"hyyfte" wrote in message
...

This is driving me mad. Why am I getting this second error? I am
combining two workbooks (and doing many other things to them) copying
tables sheet by sheet. Once the sheets in the workbook run out, an
error is given. To get around this, I added the On Error GoTo
ScanTrack step. Then it steps through the second document sheet by
sheet until the error: Run-time error '9' Subscript out of range,
stops the code. I have On Error GoTo ErrRequal. Shouldn't this be
enough to step it out of the loop without stopping the code? How do I
get past this error?



Do
With Workbooks("Package Tracking3.xls").Worksheets(i)
Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row,
15)).Copy
Cells(65536, 1).End(xlUp)(2).Select
ActiveSheet.Paste
End With
i = i + 1
On Error GoTo ScanTrack
Loop
ScanTrack:
i = 1
Do
With Workbooks("Scan Tracking3.xls").Worksheets(i)
Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row,
15)).Copy
Cells(65536, 1).End(xlUp)(2).Select
ActiveSheet.Paste
End With
i = i + 1
On Error GoTo Format
Loop
Format:


--
hyyfte
------------------------------------------------------------------------
hyyfte's Profile:
http://www.excelforum.com/member.php...o&userid=13187
View this thread: http://www.excelforum.com/showthread...hreadid=262465

Error Handling driving me crazy. Please help
 

Every error handler must have a Resume statement unless you are immediately
exiting the sub. The general format is:

Sub MyMacro()

On Error GoTo ErrorHandler
For Counter = 1 to 10
'Do something
StartAgain:
Next
Exit Sub

ErrorHandler:
'Fix problem, etc.
Resume StartAgain
End Sub

While you are "in the error handler" you have no error handling. After the
Resume error handling will be back, you do not need an other On Error:

Sub MyBadMacro()
On Error GoTo ErrorHandler
For Counter = 1 To 10
x = 1 / 0
StartAgain:
Next
Exit Sub

ErrorHandler:
Debug.Print Counter
Resume StartAgain
End Sub


If you want to just ignore the error you can use On Error Resume Next. This
is just FYI because that does not appear to be what you want to do.

--
Jim Rech
Excel MVP

"hyyfte" wrote in message
...
|
| This is driving me mad. Why am I getting this second error? I am
| combining two workbooks (and doing many other things to them) copying
| tables sheet by sheet. Once the sheets in the workbook run out, an
| error is given. To get around this, I added the On Error GoTo
| ScanTrack step. Then it steps through the second document sheet by
| sheet until the error: Run-time error '9' Subscript out of range,
| stops the code. I have On Error GoTo ErrRequal. Shouldn't this be
| enough to step it out of the loop without stopping the code? How do I
| get past this error?
|
|
|
| Do
| With Workbooks("Package Tracking3.xls").Worksheets(i)
| Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row,
| 15)).Copy
| Cells(65536, 1).End(xlUp)(2).Select
| ActiveSheet.Paste
| End With
| i = i + 1
| On Error GoTo ScanTrack
| Loop
| ScanTrack:
| i = 1
| Do
| With Workbooks("Scan Tracking3.xls").Worksheets(i)
| Range(.Cells(2, 1), .Cells(.Cells(65536, 1).End(xlUp).Row,
| 15)).Copy
| Cells(65536, 1).End(xlUp)(2).Select
| ActiveSheet.Paste
| End With
| i = i + 1
| On Error GoTo Format
| Loop
| Format:
|
|
| --
| hyyfte
| ------------------------------------------------------------------------
| hyyfte's Profile:
http://www.excelforum.com/member.php...o&userid=13187
| View this thread: http://www.excelforum.com/showthread...hreadid=262465
|