ps. Try to do a print preview, then run the code. See if there's a difference.

Dave Peterson wrote:

What do you see in the msgbox that shows what i is?

msgbox i

right before the offending line.

Kevin R wrote:

The error message that pops up is...

Run-time error '9':
Subscript out of range
<End <Debug <Help

When I click Debug it takes me to the ReDim Preserve HorzPBArray(1 To i - 1)
line.

"Dave Peterson" wrote:

And what do you see in the msgbox?

(I don't have a guess why that line causes an error.)

Kevin R wrote:

Yes, several hundred.

"Dave Peterson" wrote:

Do you have any horizontal pagebreaks in that worksheet?

If you add:
msgbox i
right before the offending line, what do you see?

Kevin R wrote:

This has worked perfect in Excel 2003 for a number of years but now I'm
getting an Error 9 at the line...

ReDim Preserve HorzPBArray(1 To i - 1)

This error seems to correspond with the upgrade to Excel 2007. Why is it no
longer working and what alternate coding can be used?

"Dave Peterson" wrote:

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:



--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson

--

Dave Peterson