How to use error handling multiple time in for loop?

Question

For Each cell In Workbooks("workbook.xlsm").Sheets("sheet1").Range("P5:P12").Cells
sheetname = cell.Value
Windows("new workbook.xlsx").Activate
Worksheets(division).Select
On Error GoTo SKIPCODE

{code}

SKIPCODE:
Next

While running this code if the sheet with name in range of P5-P12 does not exist it gives an error. FOr first such occurrence error is handled and loop continues but for second occurrence code breaks. Can someone help on this??

Check this one stackoverflow.com/questions/33877029/… and also this is a nice enough guide excelmacromastery.com/vba-error-handling — milo5m
– milo5m, Commented Oct 13, 2022 at 0:16
Very funny. You are not revealing the crucial part ({code}) for an accurate answer. — VBasic2008
– VBasic2008, Commented Oct 13, 2022 at 1:50

GWD · Accepted Answer · 2022-10-25 10:30:25Z

1

You have to reset the error handler object after having "handled" the error, in your case by just skipping the iteration. Usually this is done automatically by the resume keyword, however, since you are not using resume because you are essentially just ignoring the error, you have to do it manually by using On Error GoTo -1

Try adding that to your code like this:

For Each cell In Workbooks("workbook.xlsm").Sheets("sheet1").Range("P5:P12").Cells
    sheetname = cell.Value
    Windows("new workbook.xlsx").Activate
    Worksheets(division).Select
    On Error GoTo SKIPCODE

    '{code}

SKIPCODE:
    On Error GoTo -1
Next

edited Oct 25, 2022 at 10:30

answered Oct 13, 2022 at 7:59

GWD

4,1481 gold badge27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use error handling multiple time in for loop?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related