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.

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

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

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

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

Just the manual page breaks?

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)
End With

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)
If curWks.Rows(HorzPBArray(i)).PageBreak = xlPageBreakManual Then
Set newWks = Worksheets.Add
curWks.Rows(TopRow & ":" & HorzPBArray(i) - 1).Copy _
Destination:=newWks.Range("a1")
TopRow = HorzPBArray(i)
End If
Next i

End Sub

There was an error in my earlier post. I dropped an "end with", but it sounds
like you got it working ok.

My question. Are you breaking up a list based on a certain column. When it
changes, then create a new sheet?

If you are, you may want to use a different technique than manually inserting
page breaks.


Maybe you can steal some code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

Kevin R wrote:

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: