Inserting page break conditionally
 

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

Inserting page break conditionally
 

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

Inserting page break conditionally
 

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

Inserting page break conditionally
 

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.

Inserting page break conditionally
 

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.