Click here to Skip to main content
15,613,716 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have very big Macro code which will do one of the below functionalities.
We have templates which will describe how the report to be generated and the below code snippet it is getting the error. The functionality which it is doing is based on the template definition it will fetch the data and paste it into new sheet.

With objCurrentWorksheet
            If intTall > 1 Then
                ActiveWindow.View = xlPageBreakPreview
                    i = 1
                    .PageSetup.FitToPagesTall = False
                    Do While i <= .HPageBreaks.Count
                        Set objCell = .Range("B" & .HPageBreaks(i).Location.Row - 1)
                        Do Until objCell.Height < 15
                            Set objCell = .Range("B" & objCell.Row - 1)
                        Set objCell = .Range("B" & objCell.Row + 1)
                        >>> It is throwing the error here
                        .HPageBreaks.Add Before:=objCell
                        i = i + 1
                ActiveWindow.View = xlNormalView
            End If
'            End If
        End With

    finishTemplate = True
    Exit Function

What I have tried:

I have tried to modify the template definitions but it hasn't worked out
Updated 19-Feb-18 9:38am

It looks like you have run off the end of the HPageBreaks collection with
Do While i <= .HPageBreaks.Count
You should start traversing the collection from index 0 not 1 and stop before i equals the count.
Alternatively, use a for each construct e.g.
Dim pb As HPageBreak
For Each pb in objCurrentWorksheet.HPageBreaks 
Share this answer
Member 13638770 19-Feb-18 7:06am    
Thanks @CHill60 One other concern that after modfying as you suggested im getting this error 9: subscript out of range. Am i missing any thing here?
CHill60 19-Feb-18 7:35am    
On what line?
Member 13638770 19-Feb-18 7:40am    
On the next line .HPageBreaks.Add Before:=objCell
Member 13638770 19-Feb-18 7:42am    
Sorry the error is throwing on
Set objCell = .Range("B" & .HPageBreaks(i).Location.Row - 1)
Member 13638770 19-Feb-18 8:27am    
Am i missing anything, Because still im getting the same error 9:Subscript Out Of Range :(

Sorry the error is throwing on
Set objCell = .Range("B" & .HPageBreaks(i).Location.Row - 1)

First of all, please, read these:
You receive a "Subscript out of range" error message when you use HPageBreaks or VPageBreaks.Location in Excel[^]
Subscript out of range (Error 9)[^]

Well, you should avoid setting reference to the cell by using PageBreaks. If you trying to set page breaks, use custom method (custom logic), BUT you should clear PageBreaks settings first.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900