Unable to set PageBReak property of Range Class?

[olimits7]

I keep on getting this error when I try to run some VBA code that a former employee wrote, I can't seem to find what the issue is but this is where it stops running.

It stops at ActiveCell.PageBreak = xlPageBreakManual, can someone please point me in the right direction to try and figuring this issue out?

Columns("T:Z").EntireColumn.Hidden = True
        
    Sheets("tm").Select
        'add pagebreak
    Dim myStop As Long
    myStop = Range("R1").CurrentRegion.Rows.count
    
    For myRow = 1 To myStop
        If Cells(myRow, 1) = "" Then
            Cells(myRow + 1, 1).Select
            ActiveCell.PageBreak = xlPageBreakManual
        End If
    Next myRow

Thank you!

 

[DanteAmor]

Change this line

ActiveCell.PageBreak = xlPageBreakManual

For this

ActiveSheet.HPageBreaks.Add Before:=ActiveCell

Try and tell me.

 

[olimits7]

I got this error now "You can't add more than 1026 horizontal page breaks to a worksheet."

 

[DanteAmor]

You are adding too many page break.

You are adding a page break for each empty cell in column A.

Try this

Sub Macro1()
  Dim myRow, myStop
  Columns("T:Z").EntireColumn.Hidden = True
  Sheets("tm").Select
  'add pagebreak
[COLOR=#0000ff]  myStop = Range("A" & Rows.Count).End(xlUp).Row[/COLOR]
  For myRow = 1 To myStop
    If Cells(myRow, 1) = "" Then
      Cells(myRow + 1, 1).Select
      ActiveSheet.HPageBreaks.Add Before:=ActiveCell
    End If
  Next myRow
End Sub

 

[olimits7]

Yes, that worked...thank you so much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.