Excel macro works in Debug but not in full run

Question

So I have a macro that is designed to insert 4 header rows for each page break in the sheet's print area. It will insert the correct header rows for every page break when I run through it step by step in debug mode, however when it is running by itself it seems to be skipping parts. I have added Sleeps and Debug.Prints in order to figure out where it is going wrong and I still cannot figure it out.

Here is the code:

Sub InsertRowPageBreak()

    Dim WS As Worksheet
    Dim rng As Range
    Dim pb As Variant
    Dim Row As Integer
    Dim OffSet As Integer
    Dim InsertRow As Integer

    Set WS = ThisWorkbook.Worksheets(1)
    WS.Activate
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Dim i As Integer
    i = 1

    For Each pb In WS.HPageBreaks
        Debug.Print "Iteration: " & i
        i = i + 1

        Row = pb.Location.Row
        Range("A" & Row).Select
        Debug.Print "Page Break at Row: " & Row

        If (Range("A" & Row - 2).Value Like "*Date*") Then
            InsertRow = Row - 4
            Range("A" & InsertRow).Select
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
            Debug.Print "Inserting Page Break @ Row: " & InsertRow
        Else
            Sleep 150
            InsertRow = Row - 1
            Debug.Print "Inserting Row " & InsertRow
            If (Range("D" & InsertRow).Value Like "*Compliment*") Then
                Sleep 150
                Sheets(2).Activate
                Rows("1:4").Select
                Selection.Copy
                Sheets(1).Activate
                Range("A" & InsertRow).Select
                Selection.Insert Shift:=xlDown
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
                Debug.Print "Inserted Header 1"
            ElseIf (Range("D" & InsertRow).Value Like "*Complaint*") Then
                Sleep 150
                Sheets(2).Activate
                Rows("5:8").Select
                Selection.Copy
                Sheets(1).Activate
                Range("A" & InsertRow).Select
                Selection.Insert Shift:=xlDown
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
                Debug.Print "Inserted Header 2"
            ElseIf (Range("D" & InsertRow).Value Like "*Question*") Then
                Sleep 150
                Sheets(2).Activate
                Rows("9:12").Select
                Selection.Copy
                Sheets(1).Activate
                Range("A" & InsertRow).Select
                Selection.Insert Shift:=xlDown
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
                Debug.Print "Inserted Header 3"
            End If
            Sleep 250
        End If
        Sleep 250
    Next pb

End Sub 

When I run it in Debug mode the Debug.Print prints out

Iteration: 1
Page Break at Row: 33
Inserting Row 32
Inserted Header 1

Iteration: 2
Page Break at Row: 66
Inserting Row 65
Inserted Header 1

Iteration: 3
Page Break at Row: 94
Inserting Row 93
Inserted Header 2

Iteration: 4
Page Break at Row: 119
Inserting Row 118
Inserted Header 3

And when it runs by it's self

Iteration: 1
Page Break at Row: 33
Inserting Row 32
Inserted Header 1

Iteration: 2
Page Break at Row: 35
Inserting Row 34

Iteration: 3
Page Break at Row: 92
Inserting Row 91
Inserted Header 2

Iteration: 4
Page Break at Row: 94
Inserting Row 93

Any suggestions or help would be greatly appreciated.

Thanks, Kevin

Answer 1

After inserting a Pagebreak Excel needs to repaginate in order to update the HPageBreaks collection.

In order to allow Excel to do this while the code is running, use DoEvents in place of your Sleep's

Answer 2

I don't have access Excel at the moment, so I can't exactly answer your question, but if I were you, I would do a few things different because right now your code look very error-prone.

First off, any time you add or remove rows to a sheet via a loop, you should have it step backwards. For example:

dim i as integer

for i = mySheet.usedrange.rows.count to 1 step -1

  'Put your code here

next i

In this case, you would have to do something like loop through the pagebreaks first and identify the row numbers then loop through the row number in reverse order, but it might help.

Secondly, assign any worksheets you will be using to a variable, and reference those instead of calling the activate method. I used to use activate too, but have since learned it is a rookie mistake and I honestly can't remember why I used it in the first place. For instance, instead of doing this:

Sheets(2).Activate
Rows("1:4").Select
Selection.Copy
Sheets(1).Activate
Range("A" & InsertRow).Select

Do something like this:

dim myBook as workbook, a as worksheet, b as worksheet

set myBook = Excel.ActiveWorkbook
set a = myBook.Sheets(1)
set b = myBook.Sheets(2)

b.rows("1:4").Select
Selection.Copy
a.Range("A" & InsertRow).Select

Like I said, I know this doesn't exactly answer your question, and I would have put it in a comment if it wasn't so long, but I really think this will greatly reduce errors in the future and help with your overall code smell. If I had Excel right now I could do more. Sorry I can't be more help, but good luck!