This worked absolutely perfectly, just like I had asked. Thanks so much.
The only issue I hadn't considered is that it creates a new sheet for every
horizontal page break, not just the ones I inserted. Is there anyway to tell
it to distinguish between a hard page break (one that I forced with vba)
versus all page breaks? Thanks again for the help.
"Dave Peterson" wrote:
I put the code into a different workbook than my worksheet, and I got this
error. But it was related to the ThisWorkbook.Names.add line.
And I missed the last page using the existing routine. So I added a horizontal
page break after the last used row.
Option Explicit
Sub testme01()
Dim HorzPBArray()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim TopRow As Long
Dim i As Long
Set curWks = ActiveSheet
With curWks
.DisplayPageBreaks = False
.HPageBreaks.Add befo=.Cells.SpecialCells(xlCellTypeLastCell) _
.Offset(1, 0).EntireRow.Cells(1)
ActiveWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""" & _
ActiveSheet.Name & """)"
ActiveWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""" & _
ActiveSheet.Name & """)"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve HorzPBArray(1 To i)
HorzPBArray(i) = Evaluate("Index(hzPB," & i & ")")
i = i + 1
Wend
ReDim Preserve HorzPBArray(1 To i - 1)
TopRow = 1
For i = LBound(HorzPBArray) To UBound(HorzPBArray)
Set newWks = Worksheets.Add
curWks.Rows(TopRow & ":" & HorzPBArray(i) - 1).Copy _
Destination:=newWks.Range("a1")
TopRow = HorzPBArray(i)
Next i
End Sub
From my first post:
It actually created new workbooks, but it could be modified.
This one adds a new worksheet per "page".
Kevin R wrote:
It seems to have a problem when it comes to i - 1 at
ReDim Preserve HorzPBArray(1 To i - 1)
and
curWks.Rows(TopRow & ":" & HorzPBArray(i) - 1).Copy
Destination:=newWks.Range("a1")
If I change it to +1 the code seems to execute with error I just don't get
the results I want. This also seems to put each page break into a new
workbook. I want all the sheets to appear in the current workbook if
possible. Thanks.
"Dave Peterson" wrote:
This could get you started:
http://groups.google.co.uk/groups?th...apeXSPAM.c om
It actually created new workbooks, but it could be modified.
Kevin R wrote:
I have a program that dumps over 6000 lines of data into an excel
spreadsheet. I have managed to use vba to insert page breaks at various
points in the spreadsheet where header information is repeated. Now I'd like
to somehow divide this 1 sheet into multiple sheets with each page break
being a new sheet. I'd also like to have it give each sheet a specific name
by pulling a word out of cell that contains a specific word. Is this
possible with vba? Can anyone help get me started. Thanks.
--
Dave Peterson
--
Dave Peterson