Hi

I want to insert a page break before a cell if the cell value meets
certain conditions.
I've tried the code below which seemd to work some of the time ut not
consistently.

Sub InsertPageBreaks()

Application.ScreenUpdating = False
ActiveSheet.Activate
'ActiveSheet.ResetAllPageBreaks

lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 6 Step -1
If InStr(Cells(i, 1).Value, "---") 0 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(i,
1)
End If
Next

Application.ScreenUpdating = True
End Sub

Have you checked the InStr value when it does not work to see if it equates
to false. That is, it does not find the "---" criteria.

"RITCHI" wrote:

Hi

I want to insert a page break before a cell if the cell value meets
certain conditions.
I've tried the code below which seemd to work some of the time ut not
consistently.

Sub InsertPageBreaks()

Application.ScreenUpdating = False
ActiveSheet.Activate
'ActiveSheet.ResetAllPageBreaks

lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 6 Step -1
If InStr(Cells(i, 1).Value, "---") 0 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(i,
1)
End If
Next

Application.ScreenUpdating = True
End Sub

P.S. The ActiveSheet.Activate command is ambiguous since the sheet would
already be active.

"RITCHI" wrote:

Hi

I want to insert a page break before a cell if the cell value meets
certain conditions.
I've tried the code below which seemd to work some of the time ut not
consistently.

Sub InsertPageBreaks()

Application.ScreenUpdating = False
ActiveSheet.Activate
'ActiveSheet.ResetAllPageBreaks

lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 6 Step -1
If InStr(Cells(i, 1).Value, "---") 0 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(i,
1)
End If
Next

Application.ScreenUpdating = True
End Sub

On Sep 30, 8:12 pm, JLGWhiz wrote:
P.S. The ActiveSheet.Activate command is ambiguous since the sheet would
already be active.



"RITCHI" wrote:
Hi

I want to insert a page break before a cell if the cell value meets
certain conditions.
I've tried the code below which seemd to work some of the time ut not
consistently.

Sub InsertPageBreaks()

Application.ScreenUpdating = False
ActiveSheet.Activate
'ActiveSheet.ResetAllPageBreaks

lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 6 Step -1
If InStr(Cells(i, 1).Value, "---") 0 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(i,
1)
End If
Next

Application.ScreenUpdating = True
End Sub- Hide quoted text -

- Show quoted text -

Thanks JLGWhiz

Although I've not solved it completely the problem is related to the
Page Setup being configured for Fit to x by y pages.
Once I remove one of the fit to conditions it works, and interestingly
the page breaks are visible again in the normal view.
When the page set up includes a fit to X by y pages even though I
insert a page break manually you can't see it, but it exists.

I believe the Fit To... pages overrides any manual page breaks. It does its
own calculation of where the page breaks go to meet the Fit To criteria.

"RITCHI" wrote:

On Sep 30, 8:12 pm, JLGWhiz wrote:
P.S. The ActiveSheet.Activate command is ambiguous since the sheet would
already be active.



"RITCHI" wrote:
Hi

I want to insert a page break before a cell if the cell value meets
certain conditions.
I've tried the code below which seemd to work some of the time ut not
consistently.

Sub InsertPageBreaks()

Application.ScreenUpdating = False
ActiveSheet.Activate
'ActiveSheet.ResetAllPageBreaks

lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 6 Step -1
If InStr(Cells(i, 1).Value, "---") 0 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(i,
1)
End If
Next

Application.ScreenUpdating = True
End Sub- Hide quoted text -

- Show quoted text -

Thanks JLGWhiz

Although I've not solved it completely the problem is related to the
Page Setup being configured for Fit to x by y pages.
Once I remove one of the fit to conditions it works, and interestingly
the page breaks are visible again in the normal view.
When the page set up includes a fit to X by y pages even though I
insert a page break manually you can't see it, but it exists.