I used conditional formatting so that a particular itme does not duplicate
itself in a report. However, at the top of each page I need to disable the
conditional formatting. I would certainly appreciate it if someone could
show me how to detect where the automatic page break occurs and either disble
or remove the conditional formatting for that row or cell.

from a previous post:

DataFreakFromUtah View profile
More options May 27 2004, 11:50 am

Newsgroups: microsoft.public.excel.programming
From: (DataFreakFromUtah)
Date: 27 May 2004 09:50:19 -0700
Local: Thurs, May 27 2004 11:50 am
Subject: MANUAL & AUTOMATIC Horizontal Pagebreak Report - an example
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
No question here, just a procedure for the archive.

THIS PROCEDURE INCLUDES/RETURNS THE PRINT PAGE NUMBER
(FOR EACH MANUAL & AUTOMATIC PAGEBREAK) IN THE REPORT


Sub PageBreaksHorizontalReportALL()
'Creates a new report worksheet that contains the row of numbers
'of all MANUAL & AUTOMATIC horizontal pagebreaks on the
'active worksheet. INCLUDES PRINT PAGE NUMBER IN REPORT


Dim cell As Range
Dim PageBreakSheet As Worksheet
Dim TargetWorksheet As Worksheet
Dim hb As HPageBreak
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak
Dim NumPage As Integer


Dim Row As Integer


On Error Resume Next


'Add a new worksheet
Application.ScreenUpdating = False
Set TargetWorksheet = ActiveWorkbook.ActiveSheet
Set PageBreakSheet = ActiveWorkbook.Worksheets.Add
PageBreakSheet.Name = "Pagebreaks in " & TargetWorksheet.Name


If ActiveSheet.PageSetup.Order = xlDownThenOver Then
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
Else
VPC = ActiveSheet.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1


'Set up the column headings for Report worksheet
With PageBreakSheet
Range("A1") = "PageBreak Row"


'Optional 2nd reference that can be used to return
'a cells value that has the horizontal page break
Range("B1") = "PageBreak Cell Value"
Range("C1") = "Print Page Number"
Range("A1:C1").Font.Bold = True
End With


'Process each pagebreak
Row = 2
For Each hb In TargetWorksheet.HPageBreaks
'Derive Page Number of page break
NumPage = NumPage + VPC


With PageBreakSheet
Cells(Row, 1).Value = hb.Location.Row


'Optional 2nd reference that can be used to return
'a cell's value that has the horizontal page break on
'it's row. 'In this case it takes the value in column A
with
'the row number of the page break. Adjust
Cells(hb.Location.Row, #) as
'needed.


Cells(Row, 2).Value =
CStr(TargetWorksheet.Cells(hb.Location.Row, 1).Value)


Cells(Row, 3).Value = NumPage


Row = Row + 1


End With


Next


'Adjust column widths on Report sheet
PageBreakSheet.Columns("A:C").AutoFit
Application.StatusBar = False


'Select a cell on the top of the report worksheet
Range("A2").Select


End Sub


you can always search the newsgroup, too.
hope this helps
susan


On Feb 28, 10:37 am, JR_06062005
wrote:
I used conditional formatting so that a particular itme does not duplicate
itself in a report. However, at the top of each page I need to disable the
conditional formatting. I would certainly appreciate it if someone could
show me how to detect where the automatic page break occurs and either disble
or remove the conditional formatting for that row or cell.

It does appear that buried in this procedure is the answer to my question.
Thanks

"Susan" wrote:

from a previous post:

DataFreakFromUtah View profile
More options May 27 2004, 11:50 am

Newsgroups: microsoft.public.excel.programming
From: (DataFreakFromUtah)
Date: 27 May 2004 09:50:19 -0700
Local: Thurs, May 27 2004 11:50 am
Subject: MANUAL & AUTOMATIC Horizontal Pagebreak Report - an example
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
No question here, just a procedure for the archive.

THIS PROCEDURE INCLUDES/RETURNS THE PRINT PAGE NUMBER
(FOR EACH MANUAL & AUTOMATIC PAGEBREAK) IN THE REPORT


Sub PageBreaksHorizontalReportALL()
'Creates a new report worksheet that contains the row of numbers
'of all MANUAL & AUTOMATIC horizontal pagebreaks on the
'active worksheet. INCLUDES PRINT PAGE NUMBER IN REPORT


Dim cell As Range
Dim PageBreakSheet As Worksheet
Dim TargetWorksheet As Worksheet
Dim hb As HPageBreak
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak
Dim NumPage As Integer


Dim Row As Integer


On Error Resume Next


'Add a new worksheet
Application.ScreenUpdating = False
Set TargetWorksheet = ActiveWorkbook.ActiveSheet
Set PageBreakSheet = ActiveWorkbook.Worksheets.Add
PageBreakSheet.Name = "Pagebreaks in " & TargetWorksheet.Name


If ActiveSheet.PageSetup.Order = xlDownThenOver Then
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
Else
VPC = ActiveSheet.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1


'Set up the column headings for Report worksheet
With PageBreakSheet
Range("A1") = "PageBreak Row"


'Optional 2nd reference that can be used to return
'a cells value that has the horizontal page break
Range("B1") = "PageBreak Cell Value"
Range("C1") = "Print Page Number"
Range("A1:C1").Font.Bold = True
End With


'Process each pagebreak
Row = 2
For Each hb In TargetWorksheet.HPageBreaks
'Derive Page Number of page break
NumPage = NumPage + VPC


With PageBreakSheet
Cells(Row, 1).Value = hb.Location.Row


'Optional 2nd reference that can be used to return
'a cell's value that has the horizontal page break on
'it's row. 'In this case it takes the value in column A
with
'the row number of the page break. Adjust
Cells(hb.Location.Row, #) as
'needed.


Cells(Row, 2).Value =
CStr(TargetWorksheet.Cells(hb.Location.Row, 1).Value)


Cells(Row, 3).Value = NumPage


Row = Row + 1


End With


Next


'Adjust column widths on Report sheet
PageBreakSheet.Columns("A:C").AutoFit
Application.StatusBar = False


'Select a cell on the top of the report worksheet
Range("A2").Select


End Sub


you can always search the newsgroup, too.
hope this helps
susan


On Feb 28, 10:37 am, JR_06062005
wrote:
I used conditional formatting so that a particular itme does not duplicate
itself in a report. However, at the top of each page I need to disable the
conditional formatting. I would certainly appreciate it if someone could
show me how to detect where the automatic page break occurs and either disble
or remove the conditional formatting for that row or cell.

i'm sorry.
i'm just batting 1000 today!
maybe i should shut up for the rest of the day.
:)
susan

On Feb 28, 11:25 am, JR_06062005
wrote:
It does appear that buried in this procedure is the answer to my question.
Thanks



"Susan" wrote:
from a previous post:

DataFreakFromUtah View profile
More options May 27 2004, 11:50 am

Newsgroups: microsoft.public.excel.programming
From: (DataFreakFromUtah)
Date: 27 May 2004 09:50:19 -0700
Local: Thurs, May 27 2004 11:50 am
Subject: MANUAL & AUTOMATIC Horizontal Pagebreak Report - an example
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
No question here, just a procedure for the archive.

THIS PROCEDURE INCLUDES/RETURNS THE PRINT PAGE NUMBER
(FOR EACH MANUAL & AUTOMATIC PAGEBREAK) IN THE REPORT

Sub PageBreaksHorizontalReportALL()
'Creates a new report worksheet that contains the row of numbers
'of all MANUAL & AUTOMATIC horizontal pagebreaks on the
'active worksheet. INCLUDES PRINT PAGE NUMBER IN REPORT

Dim cell As Range
Dim PageBreakSheet As Worksheet
Dim TargetWorksheet As Worksheet
Dim hb As HPageBreak
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak
Dim NumPage As Integer

Dim Row As Integer

On Error Resume Next

'Add a new worksheet
Application.ScreenUpdating = False
Set TargetWorksheet = ActiveWorkbook.ActiveSheet
Set PageBreakSheet = ActiveWorkbook.Worksheets.Add
PageBreakSheet.Name = "Pagebreaks in " & TargetWorksheet.Name

If ActiveSheet.PageSetup.Order = xlDownThenOver Then
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
Else
VPC = ActiveSheet.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1

'Set up the column headings for Report worksheet
With PageBreakSheet
Range("A1") = "PageBreak Row"

'Optional 2nd reference that can be used to return
'a cells value that has the horizontal page break
Range("B1") = "PageBreak Cell Value"
Range("C1") = "Print Page Number"
Range("A1:C1").Font.Bold = True
End With

'Process each pagebreak
Row = 2
For Each hb In TargetWorksheet.HPageBreaks
'Derive Page Number of page break
NumPage = NumPage + VPC

With PageBreakSheet
Cells(Row, 1).Value = hb.Location.Row

'Optional 2nd reference that can be used to return
'a cell's value that has the horizontal page break on
'it's row. 'In this case it takes the value in column A
with
'the row number of the page break. Adjust
Cells(hb.Location.Row, #) as
'needed.

Cells(Row, 2).Value =
CStr(TargetWorksheet.Cells(hb.Location.Row, 1).Value)

Cells(Row, 3).Value = NumPage

Row = Row + 1

End With

Next

'Adjust column widths on Report sheet
PageBreakSheet.Columns("A:C").AutoFit
Application.StatusBar = False

'Select a cell on the top of the report worksheet
Range("A2").Select

End Sub

you can always search the newsgroup, too.
hope this helps
susan

On Feb 28, 10:37 am, JR_06062005
wrote:
I used conditional formatting so that a particular itme does not duplicate
itself in a report. However, at the top of each page I need to disable the
conditional formatting. I would certainly appreciate it if someone could
show me how to detect where the automatic page break occurs and either disble
or remove the conditional formatting for that row or cell.- Hide quoted text -

- Show quoted text -

Sorry, didn't mean to sound unappreciative. Your answer did help tremendously.

"Susan" wrote:

i'm sorry.
i'm just batting 1000 today!
maybe i should shut up for the rest of the day.
:)
susan

On Feb 28, 11:25 am, JR_06062005
wrote:
It does appear that buried in this procedure is the answer to my question.
Thanks



"Susan" wrote:
from a previous post:

DataFreakFromUtah View profile
More options May 27 2004, 11:50 am

Newsgroups: microsoft.public.excel.programming
From: (DataFreakFromUtah)
Date: 27 May 2004 09:50:19 -0700
Local: Thurs, May 27 2004 11:50 am
Subject: MANUAL & AUTOMATIC Horizontal Pagebreak Report - an example
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
No question here, just a procedure for the archive.

THIS PROCEDURE INCLUDES/RETURNS THE PRINT PAGE NUMBER
(FOR EACH MANUAL & AUTOMATIC PAGEBREAK) IN THE REPORT

Sub PageBreaksHorizontalReportALL()
'Creates a new report worksheet that contains the row of numbers
'of all MANUAL & AUTOMATIC horizontal pagebreaks on the
'active worksheet. INCLUDES PRINT PAGE NUMBER IN REPORT

Dim cell As Range
Dim PageBreakSheet As Worksheet
Dim TargetWorksheet As Worksheet
Dim hb As HPageBreak
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak
Dim NumPage As Integer

Dim Row As Integer

On Error Resume Next

'Add a new worksheet
Application.ScreenUpdating = False
Set TargetWorksheet = ActiveWorkbook.ActiveSheet
Set PageBreakSheet = ActiveWorkbook.Worksheets.Add
PageBreakSheet.Name = "Pagebreaks in " & TargetWorksheet.Name

If ActiveSheet.PageSetup.Order = xlDownThenOver Then
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
Else
VPC = ActiveSheet.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1

'Set up the column headings for Report worksheet
With PageBreakSheet
Range("A1") = "PageBreak Row"

'Optional 2nd reference that can be used to return
'a cells value that has the horizontal page break
Range("B1") = "PageBreak Cell Value"
Range("C1") = "Print Page Number"
Range("A1:C1").Font.Bold = True
End With

'Process each pagebreak
Row = 2
For Each hb In TargetWorksheet.HPageBreaks
'Derive Page Number of page break
NumPage = NumPage + VPC

With PageBreakSheet
Cells(Row, 1).Value = hb.Location.Row

'Optional 2nd reference that can be used to return
'a cell's value that has the horizontal page break on
'it's row. 'In this case it takes the value in column A
with
'the row number of the page break. Adjust
Cells(hb.Location.Row, #) as
'needed.

Cells(Row, 2).Value =
CStr(TargetWorksheet.Cells(hb.Location.Row, 1).Value)

Cells(Row, 3).Value = NumPage

Row = Row + 1

End With

Next

'Adjust column widths on Report sheet
PageBreakSheet.Columns("A:C").AutoFit
Application.StatusBar = False

'Select a cell on the top of the report worksheet
Range("A2").Select

End Sub

you can always search the newsgroup, too.
hope this helps
susan

On Feb 28, 10:37 am, JR_06062005
wrote:
I used conditional formatting so that a particular itme does not duplicate
itself in a report. However, at the top of each page I need to disable the
conditional formatting. I would certainly appreciate it if someone could
show me how to detect where the automatic page break occurs and either disble
or remove the conditional formatting for that row or cell.- Hide quoted text -

- Show quoted text -