Works with F8, not when running

[dchaney]

Hello All,

I am trying to figure out why my code will work for each iteration of an HPageBreak when I use F8, but when I run it fully it only works on the first iteration, but then it skips the rest. Is there something I am missing within my code, and if so can someone help me with it? here is my code:

'******************************************************************************************************************************
'Move Page Breaks above current selected cell
'******************************************************************************************************************************
Dim i As Integer, pb As Integer, pbRow As Integer

i = 1
pb = ActiveSheet.HPageBreaks.Count
Do
    If pb = 0 Then
        Exit Do
    End If
    
    pbRow = ActiveSheet.HPageBreaks(i).Location.Row
    
    If Not IsEmpty(Range("B" & pbRow).Value) Then
    'Cell is not empty therefore find the first occurrence of blank in Col B above this row
        Do
            'Loopback until there is an empty cell in Col B
            pbRow = pbRow - 1
        Loop Until IsEmpty(Range("B" & pbRow))
            'Set the new Page break above the empty cell
            Range("B" & pbRow + 1).Select
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
            
    End If
    
    i = i + 1

Loop Until i > pb

Any help will be appreciated, thanks in advance

 

[dchaney]

Hello All,

I figured this out, so wanted to post my results in case others run into this same issue. I made the following adjustments to the code. The change that allowed for me to get this to work was adding ActiveWindow.View = xlPageBreakPreview, everything else was clean up trying to figure it all out.

'******************************************************************************************************************************
'Move Page Breaks above current Selected Cells
'******************************************************************************************************************************
ActiveWindow.View = xlPageBreakPreview

i = 1
pb = ActiveSheet.HPageBreaks.Count

Do Until i > pb
    
    pbRow = ActiveSheet.HPageBreaks(i).Location.Row
    
    If Not IsEmpty(Range("B" & pbRow).Value) Then     'Cell is not empty therefore find the first blank cell in Col B above this row
    
        Do Until IsEmpty(Range("B" & pbRow))          'Loopback until there is an empty cell in Col B
            pbRow = pbRow - 1
        Loop
            
        Range("B" & pbRow + 1).Select                                                         'Select the cell below the empty cell
        ActiveSheet.SelectedSheets.HPageBreaks.Add Before:=ActiveCell            'Set the new Page break above the selected cell
            
    End If
    
    i = i + 1

Loop

ActiveWindow.View = xlNormalView