D
DataFreakFromUtah
No question here, just a procedure for the archive.
Search critera: insert manual horizontal pagebreak page break at first
instance occurance time page of criteria only. prevent exclude
repeating redundant horizontal pagebreaks compare one range to another
range
Note: the purpose of this routine was to quickly create manual/forced
horizontal page breaks
for a large spreadsheet report with many deparment numbers. The first
instance or page of a department number would get a manual horizontal
pagebreak at the cell with the department number. But the procedure
would not insert a manual
pagebreak for repeated occurances of the department number's second
through nth...pages in the report. The procedure would also format the
cell font bold for this first page of the department.
Why is this important? Because the Excel file was going to be printed
to an Adobe Acrobat pdf file and each department number needed to
start on a fresh page (no page in the pdf file to have information for
two or more depts).
Next, an Adobe Acrobat 3rd party plug-in named ARTS PDF
Bookmarker(www.artspdf.com) was going to be usedto create bookmarks in
the pdf file based on the department number having a bold font for the
first page of the department number.Then a second Adobe Acrobat 3rd
party plug-in from ARTS PDF (Merge & Split Lite) was used to split the
pdf file
into separate files based on the bookmarks created from the previous
step. Then these separate pdf files would be used to publish to a
intranet website.
Sub HoriztonalPageBreakInsertFirstInstanceOnly()
'DATA is a named range in the workbook. DATA contains the base data
for
'comparing to your "working" worksheet.
Dim cell As Range
Dim Found As Boolean
On Error Resume Next
For Each cell In [A1:A5000] ' This range contains your comparison
data
'on your active worksheet and the cell(s) will be edited or altered
'based on your desired task you set below.
Found = WorksheetFunction.Match(cell, [DATA], 0)
'Compare the current evaluted value to the previous
'match to the values in the range named DATA (set to x)
If cell.Value = x Then
GoTo here
End If
If Found And Not cell.Value = "" Then
'Edit the section below to do edit/perform your desired task.
'In this example, the cell font is turned bold and horizontal
'pagebreak is inserted
cell.Font.Bold = True
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=cell
'set the value for the first instance of a match to the
'values in range named DATA
x = cell.Value
Found = False
End If
here:
Next cell
End Sub
Search critera: insert manual horizontal pagebreak page break at first
instance occurance time page of criteria only. prevent exclude
repeating redundant horizontal pagebreaks compare one range to another
range
Note: the purpose of this routine was to quickly create manual/forced
horizontal page breaks
for a large spreadsheet report with many deparment numbers. The first
instance or page of a department number would get a manual horizontal
pagebreak at the cell with the department number. But the procedure
would not insert a manual
pagebreak for repeated occurances of the department number's second
through nth...pages in the report. The procedure would also format the
cell font bold for this first page of the department.
Why is this important? Because the Excel file was going to be printed
to an Adobe Acrobat pdf file and each department number needed to
start on a fresh page (no page in the pdf file to have information for
two or more depts).
Next, an Adobe Acrobat 3rd party plug-in named ARTS PDF
Bookmarker(www.artspdf.com) was going to be usedto create bookmarks in
the pdf file based on the department number having a bold font for the
first page of the department number.Then a second Adobe Acrobat 3rd
party plug-in from ARTS PDF (Merge & Split Lite) was used to split the
pdf file
into separate files based on the bookmarks created from the previous
step. Then these separate pdf files would be used to publish to a
intranet website.
Sub HoriztonalPageBreakInsertFirstInstanceOnly()
'DATA is a named range in the workbook. DATA contains the base data
for
'comparing to your "working" worksheet.
Dim cell As Range
Dim Found As Boolean
On Error Resume Next
For Each cell In [A1:A5000] ' This range contains your comparison
data
'on your active worksheet and the cell(s) will be edited or altered
'based on your desired task you set below.
Found = WorksheetFunction.Match(cell, [DATA], 0)
'Compare the current evaluted value to the previous
'match to the values in the range named DATA (set to x)
If cell.Value = x Then
GoTo here
End If
If Found And Not cell.Value = "" Then
'Edit the section below to do edit/perform your desired task.
'In this example, the cell font is turned bold and horizontal
'pagebreak is inserted
cell.Font.Bold = True
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=cell
'set the value for the first instance of a match to the
'values in range named DATA
x = cell.Value
Found = False
End If
here:
Next cell
End Sub