Help with VBA to add page breaks that stopped working

[rickadams]

I have a Macro that was supplied to me earlier in 2018 from this group, and it will not work any more.
I get the following error.
Run Time Error 1004 Application-Defined or Object-Defined Error.
Sub insertpagebreaks()
' this macro adds page breaks if data in column A changes for printing out new page for each time there is a change.
Dim I As Long, J As Long
J = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For I = J To 2 Step -1
If Range("A" & I).Value <> Range("A" & I - 1).Value Then
ActiveSheet.HPageBreaks.Add Before:=Range("A" & I) '<----- This is the line of code that shows the error in Debug
End If
Next I
End Sub


THANK YOU

 

[rlv01]

I ran your code as-is and it worked fine for me. No errors. You might try changing the implied range references to explicit definitions as I have done below, and see if that makes a difference.

Private Sub insertpagebreaks()
' this macro adds page breaks if data in column A changes for printing out new page for each time there is a change.
    Dim I As Long, J As Long
    
    J = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    For I = J To 2 Step -1
        If ActiveSheet.Range("A" & I).Value <> ActiveSheet.Range("A" & I - 1).Value Then
            ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Range("A" & I)    '<----- This is the line of code that shows the error in Debug
        End If
    Next I

End Sub

(Tip: when posting your code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

as it makes the code easier to read.)

 

[rickadams]

I ran your code as-is and it worked fine for me. No errors. You might try changing the implied range references to explicit definitions as I have done below, and see if that makes a difference.

Private Sub insertpagebreaks()
' this macro adds page breaks if data in column A changes for printing out new page for each time there is a change.
    Dim I As Long, J As Long
   
    J = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    For I = J To 2 Step -1
        If ActiveSheet.Range("A" & I).Value <> ActiveSheet.Range("A" & I - 1).Value Then
            ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Range("A" & I)    '<----- This is the line of code that shows the error in Debug
        End If
    Next I

End Sub

(Tip: when posting your code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

as it makes the code easier to read.)

Thank you So much! That worked perfectly... Also
it says If you received at least one reply answering your question and solving your problem,
then please remember to click the "Mark as solution" button - - right next to the best post which is answering your question.
I would like to mark as solution button but it is not there so I am not able to do that.

 

[rickadams]

Thank you So much! That worked perfectly... Also
it says If you received at least one reply answering your question and solving your problem,
then please remember to click the "Mark as solution" button - - right next to the best post which is answering your question.
I would like to mark as solution button but it is not there so I am not able to do that.

I was able to mark as a solution however the option was not there until after I posted my reply, Thank you again!

 

[Fluff]

In future please mark the post that contains the solution, rather than your post saying it works.
I have done that for you this time.