[SOLVED] Error 1004 when setting HPageBreak location

Error 1004 when setting HPageBreak location

I moved a pagebreak in an Excel sheet by hand and recorded the following macro in the process because I want to automate it:

Sub RecordedMacro()
'
' RecordedMacro Macro
'
    Set ActiveSheet.HPageBreaks(1).Location = Range("A71")

End Sub

Running this recorded macro fails with an error 1004 ("application-defined or object-defined error"), even with an active sheet where I can do it manually without any problems.

I did some Internet searching and it appears that most people end up using HPageBreaks.Add instead of changing a pagebreak's location, but I'd like to know why this macro fails and if there is a way to make it work as I see no reason for throwing an error.

EDIT: The following line fails with an 1004, too:

Set ActiveSheet.HPageBreaks(1).Location = ActiveSheet.HPageBreaks(1).Location

Solution

My answer is slightly different than what you discovered, and doesn't require the ResetAllPageBreaks. In addition this one toggles ScreenUpdating to avoid the flicker of going back and forth:

Sub MoveThatHBreak()
Application.ScreenUpdating = False
ActiveWindow.View = xlPageBreakPreview
Set ActiveSheet.HPageBreaks(1).Location = Range("A11")
ActiveWindow.View = xlNormalView
Application.ScreenUpdating = True
End Sub

This is in Excel 2016. I stumbled on this answer because I could only record the move while in Page Break Preview, so figured maybe the same was true in VBA.