Macro keeps stopping in the same place when run all at once, but runs line-by-line

Hi all,

I just started using VBA very recently and haven't had any official training. I've been using the loop below in order to move data from worksheets into a single form that can be printed out, and it's been working perfectly fine until just recently. I'm not sure if something I've done outside of the loop (the subroutine is rather large in order to make the form a one-click button) has somehow destabilized what's in here, and I would greatly appreciate any help.

The "LastRow2 = Cells.Find(What:="Div. 10 SH & N Packinghouse - 1J", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row" statement is causing the macro to stop and move to debugging mode, highlighting the line, but not displaying an error. If I run the macro line by line in debugging mode, there are no errors and the macro runs normally.

Excel 2010

Please Login or Register  to view this content.

Sanitation and Machine ID Master List v2.xlsm

I've attached a version of the workbook I'm working on. The workbook(v2) works some of the time, but other times it will halt on the aforementioned statement. It's really almost a coin toss whether or not it will work.

I just listed the section initially in case there was something wrong with my syntax or some mistyping error.

What the loop is doing with the 'problem' cells.find statement above is to go to sheets whose names are listed as Cases, e.g. Line A, Line B, Line C, etc.

Whenever it goes to one of these worksheets (for example Line B), it determines what the last row in the A column is, then makes a string of the address of A2 to G(lastrow).

It then goes to the WSName worksheet, the main worksheet where the form is being created, and determines what row the next section starts in order to insert a number of rows right above it, pushing the next section down, that will fit the future copy range.

It then inserts the new sub-section name (Line B Equipment), copies the range from the Line B sheet, and pastes it in the WSName worksheet in the range it created by inserting rows.


It then goes to the next case "Line C", and starts over again

With this loop, somehow and SOMETIMES the cells.find statement (the one that is finding the Title of the next section) is making the macro enter debugging mode when the whole macro is run at once. Other times it works perfectly. The cell it's trying to find is always there (the title's name / cell.value never changes), so I'm not sure where exactly the error is coming from, especially since it's not happening all the time.