How to skip the codes of errorhandler in a for next loop?

[ironny90]

Hi everyone! Just ran into this issue and not sure how to get around it. So the macro is looping through all the excel files in a folder, open each one and make changes. However, a few of the files have different tab names, therefore I want to give the user a notification regarding which files are different so that they can change them later.

The problem is the code keeps running after activeworkbook.close and pop up the msgbox "Please check names of the external tabs..." If I put "Next" before the ErrorHandler, it will not go to the next file... Let me know if you know how to solve the issue and many thanks in advance!!

Set fso = CreateObject("scripting.filesystemobject")
Set ff = fso.getfolder(folderpath)
For Each file In ff.Files
Workbooks.Open file
On Error GoTo ErrorHandler
.
.
.
ActiveWorkbook.Save
ActiveWorkbook.Close ' for good files
ErrorHandler:
MsgBox "Please check names of the external tabs..."
ActiveWorkbook.Close SaveChanges:=False 'for bad files
Next
End If

 

[Joe4]

You should put your Error Handler code at the bottom/end of your code (it should NEVER be put in the middle of a loop!).
Then, just put an "Exit Sub" line of code just before the error handler.
Then, if your code gets to that point without error, it will exit the sub without running that code.

See here for more details: Excel VBA Error Handling - All You Need to Know!

 

[ironny90]

@Joe4 Thank you so much for your quick response!! What if I want the codes to continue running after the error msg? Like the user notes which file is different and the codes continues to the next file? Is it doable?

 

[Joe4]

@Joe4 Thank you so much for your quick response!! What if I want the codes to continue running after the error msg? Like the user notes which file is different and the codes continues to the next file? Is it doable?

If you want it to return to the code, just include the line "Resume Next" in your error handling code to return to the code.
You can also use "Err.Clear" to clear any error message before resuming, if you like.

 

[ironny90]

@Joe4 Ah I see. Thank you!!! I will let the codes exit after errorhandler.

 

[Joe4]

You are welcome.