trying to catch error 1004, don't work in 2nd loop...

[RompStar]

Hi there...

ThisWorkbook.Sheets represents the workbook that has all the sheets
that will be used for the export to different folders. ws.Copy will
copy the first sheet from ThisWorkbook.Sheets and create a new
workbook basically, as far as I understand. So then I have to refer to
the new sheet in ActiveWorkbook (which would represent the first split
off sheet from the master workbook that has them all). I just wanted
to clarify what the script is doing, anyways, it's strange and I can't
seem to figure it out, I would like to handle the errors 1004 and save
them using file path of StoreDir2.

Basically, when the code hits this line:

ActiveWorkbook.SaveAs StoreDir & "Store_" & ws.Name & endoffilename &
".xls"

It's supposed to save to the StoreDir found in the case statement and
this usually works flawless, unless the directory folder got deleted,
changed or wasn't made yet, then Excel can't save the file and results
in error 1004 as far as I know. So I wanted to forward any errors to
the Error Handling block: ErrHandler:

So let's say I have 4 sheets to process in the ws, they are 1, 6000,
2, 5432

The 6000 and 5432 would not be found in the Case Else as a default
address to use for numbers that don't exist, this works great. So the
first time around for sheet with number 1 in it, that address would be
found in the Case, but I deleted it's Store folder on purpose to
create the error 1004 in Saving and to catch that and to process it
into a different folder which is what StoreDir2 is. The first time
around this works exactly, then next Store in 6000 that address will
be gotten from Case Else, works....

Then Store 2, it generates error 1004, but this time the VBA just
quits with the error 1004 and it dones't go to the error handling
block.

So that is my question, in the first loop it does, but on the 3rd loop
when the 1004 error is encountered, it's not going to the ErrHandler:

Why ? How can I make it better ?

Any ideas anyone ?



Sub Export_test()

Dim ws As Worksheet
Dim StoreDir, StoreDir2 As String
Dim pg As Workbook
Dim endoffilename As String

Set pg = Workbooks.Open(Filename:="c:\path\temp_pg.xls")
StoreDir2 = "c:\Documents and Settings\lanid\Desktop
\save_file_1004_error\"

endoffilename = InputBox("Enter the name of the Excel file name to be
used: ")

For Each ws In ThisWorkbook.Sheets

ws.Copy
StoreDir = ws.Name

On Error GoTo ErrHandler

Select Case StoreDir
Case Is = 1, 2, 4, 5, 6, 9, 10, 12, 20, 21, 23, 24, 25, 26, 81, 82
StoreDir = "c:\Documents and Settings\lanid\Desktop\stores\" & ws.Name
& "\"
Case Else
StoreDir = "c:\Documents and Settings\lanid\Desktop\no_match_store_dump
\"
End Select

ActiveWorkbook.SaveAs StoreDir & "Store_" & ws.Name & endoffilename &
".xls"
ActiveWorkbook.Close True

1:
Next

ErrHandler:

If Err.Number = 1004 Then
ActiveWorkbook.SaveAs StoreDir2 & "Store_" & ws.Name & endoffilename &
".xls"
ActiveWorkbook.Close True
GoTo 1
Else
MsgBox "The message text of the error is: " & Error(Err)
End If

End Sub

 

[Jim Rech]

Every error handler must have a Resume:

ErrHandler:

If Err.Number = 1004 Then
ActiveWorkbook.SaveAs StoreDir2 & "Store_" & ws.Name & endoffilename &
".xls"
ActiveWorkbook.Close True
GoTo 1 ''<<<<<<< Use Resume instead.
Else
MsgBox "The message text of the error is: " & Error(Err)
End If

 

[RompStar]

So should I use Resume 1

or just resume ? because I would like it to resume at a specific
location, as it would try to execute the next line of code
and I want it to start at the top of the loop.

RompStar