Problem with Excel page breaks

[Stoffel24]

Hi all

I am having trouble with inserting manual page breaks

My code reads as follows:

Sub PageSetup()
'** Here we manually set a page break

Dim PgeBrk As Excel.HPageBreak

ActiveWindow.View = xlPageBreakPreview
For Each PgeBrk In Application.ActiveSheet.HPageBreaks

'Do stuff here and go to new location

Worksheets("Tensile-Bend&quot.Rows(ActiveCell.Row + 1).PageBreak = xlPageBreakManual

Next PgeBrk
ActiveWindow.View = xlNormalView

End Sub
The problem is that even when there are several pages of data, when it gets to the part "For Each PgeBrk In Application.ActiveSheet.HPageBreaks" it gives me a subscript out of range error. If I return a count of HPagebreaks it returns 2 or 3 so we shouldn't be getting that error. Also, when I debug, I fiddle about but don't really change anything and then click on run again and it works - almost as if the first time round, it hadn't yet realised that there were a few pages of data.

Any ideas as to what is happening?

Thanks a lot.

 

[andrew299]

stoffel24
Your code seems to work fine on my sheet first time. The only time I had the error was when I forgot to change the name of my work sheet to the one you specified (Tensile-Bend) Does your code change the sheet at all? If so perhaps a line like

sheets("Tensile-Bend&quot.select

after the 'do stuff here line . might help

try it and see.

Andrew299

 

[xlbo]

Not sure what is exactly wrong with your code but I think you would be better off using the ADD method of Hpagebreaks

ActiveSheet.HPageBreaks.Add Before:=range("A" & activecell.row +1) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Stoffel24]

Thanks guys. This is one of those intermitent errors - it seems to work fine sometimes and other times not. I haven't quite figured it out but it may be PC specific. This code runs on several machines, only one of which seems to be giving problems. It does work on my PC which makes diagnosing a bit of a problem. I was hoping someone would point out something that would cause glitches in some cases. Any further ideas!?? [Indian]

 

[andrew299]

I am running 2000 on my machine but when I change over to NT some commands arent accepted. There is generally a way around the problem.

 

[andrew299]

Nope runs fine on my NT machine, What version of Excel and windows are you running?

 

[RobBroekhuis]

Just like the usedrange property sometimes needs to be reset, I suspect that the same may apply to pagebreaks. One way that may work (I haven't tested it) is

dummy=activesheet.hpagebreaks.count

Actually, I did just test, but like the others above, I don't get the problem in the first place. But Excel does "think" for a bit when I execute the command above, so it may help reset its internal pagebreak locations.
Rob