Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert HPageBreaks first instance occurance only - an example
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 Befo=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 he Next cell End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert HPageBreaks first instance occurance only - an example
Seems like it would be easier to just loop through the range and insert a
pagebreak everytime the current cell doesn't equal the previous cell. -- Regards, Tom Ogilvy "DataFreakFromUtah" wrote in message om... 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 Befo=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 he Next cell End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there no one who uses PageBreaks? HPageBreaks? | Excel Worksheet Functions | |||
HPageBreaks.Add | Excel Worksheet Functions | |||
Insert HPageBreaks | Excel Programming | |||
Can't set manual HPageBreaks | Excel Programming | |||
Can't set manual HPageBreaks | Excel Programming |