Recorded Macro to Set page breaks generates error.

[Guest]

I recorded a Macro to generate page breaks. The code is:

ActiveSheet.ResetAllPageBreaks
Set ActiveSheet.HPageBreaks(1).Location = Range("A68")

When I try to run this macro, I get Runtime Error '1004', Application or
Object defined error. Does anybody know of a way to do this?

 

[JulieD]

Hi Jared

HPageBreaks is a read-only property

try (from HELP)
This example sets a manual page break above row 25 on Sheet1.

Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual

 

[Sharad]

Hi Jared,

You can use code like below, if you want to add pagebreak after row 30.

Sub pageBreak_Example()
ActiveSheet.ResetAllPageBreaks
ActiveSheet.HPageBreaks.Add Before:=Range("A31")
End Sub

Sharad

 

[Guest]

I've entered the following code:

Workbooks(ExWBName).Sheets("Market Pene.").PageSetup.FitToPagesWide = 1
Workbooks(ExWBName).Sheets("Market Pene.").PageSetup.FitToPagesTall = 2
Workbooks(ExWBName).Sheets("Market Pene.").Rows(68).PageBreak =
xlPageBreakManual

It runs, but it is not generating a break at row 68.

 

[JulieD]

Hi Jared

if you're telling it to "fit to pages tall = 2" then this overrides the page
break command.

Comment out that line and try your code again.
Workbooks(ExWBName).Sheets("Market Pene.").PageSetup.FitToPagesWide = 1
'Workbooks(ExWBName).Sheets("Market Pene.").PageSetup.FitToPagesTall = 2
Workbooks(ExWBName).Sheets("Market Pene.").Rows(68).PageBreak =
xlPageBreakManual

Cheers
JulieD

 

[Guest]

Thanks for the help. I have added the page break. I would now like to delete
all other pagebreaks. The help says that there is a delete method for the
hpagebreaks collection, but I can't get it to work. Any ideas?

 

[JulieD]

Hi Jared

in my version - Help says that HPageBreaks is read-only ...
however, there is a PageBreaks property
and from Help on this property
**
To remove all manual page breaks on a worksheet, set Cells.PageBreak to
xlPageBreakNone.
**

Cheers
JulieD

 

[Guest]

Thanks for the help Julie,

Specifically, I want to print a page 1 page wide by 2 pages long. I want the
vertical page brake to occur at line 68. It automatically wants to appear at
line 64. Interactively, I can drag the page break from 64 to 68. I just can't
find a way to to do it programatically. By commenting out the
fitropagestall=2, I end up with a new page between lines 64 and 68 to give me
three pages total. Any ideas?

 

[Guest]

Julie,

Thanks for the help

I want to my printout to be 1 page wide by 2 pages long. I want the page
break to be a line 68. I can manually drag the pagebreak to line 68, but I
can't seem to automate it. If I comment the line out like you suggest, I get
an extra page break, and three pages. Do you have any idea what I should try
now?

 

[Guest]

Julie,

This is coppied from the help in Excel 2002:

For the HPageBreak and VPageBreak objects, this property returns or sets the
cell (a Range object) that defines the page-break location. Horizontal page
breaks are aligned with the top edge of the location cell; vertical page
breaks are aligned with the left edge of the location cell. Read/write Range.

Example
This example moves the horizontal page-break location.

Worksheets(1).HPageBreaks(1).Location = Worksheets(1).Range("e5")

 

[JulieD]

Hi Jared

i've got ver 2003 ... from HELP
---
HPageBreaks Property
Returns an HPageBreaks collection that represents the horizontal page breaks
on the sheet. Read-only.

--

but i'm not sure how to achieve what you want to do ... might need to have a
look at scaling rather than page breaks?

Cheers
JulieD

 

[Sharad]

Hi Jared,
You can delete an HPageBreak if you know which one to delete.

for example
Sheet1.HPageBreak.Item(1).Delete

This will work, provided
1) the used range is atleast up to the HPageBreak.
2) The HPageBreak is not the default excel page break (which normally is
before row 56).

SO tell me what exactly you are trying to do?
Any chance that you are confusing between, PageBreakUp and Print Area? I
got feeling that, may be what you want is actually to set a right print
area.

Sharad

 

[Guest]

Sharad,

I want to my printout to be 1 page wide by 2 pages long. I want the page
break to be a line 68. I can manually drag the pagebreak to line 68, but I
can't seem to automate it. I want to print both pages, not just one, so
unless I have a macro the changes the print area, I will have to stick with
attempting to manipulate the pagebreak objects as far as I can tell.

 

[Sharad]

Well Jared, got what you want.
You need not add the page break, but you drag the default page break.

Copy below code as it is: Make changes to the sheet name.
Also I set the print area, down to row 68 and right to column K, you can
change the column as per your need.
Finally at the last line, the print area is expaned till row 1000. You
can delete this line, if the final print out
is single paper, or set it to what ever you like.

The DragOff command doesn't work unless you are Page Break View, so
below code sets page break view before dragging and sets it back to
normal after dragging.

Sharad


Sub PrintGimmick()
With Worksheets("Sheet1")
.PageSetup.PrintArea = "" 'clear exisitng.
.PageSetup.PrintArea = "$A$1:$K$68" 'set it right
.ResetAllPageBreaks 'this is must.
.Activate
End With
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.HPageBreaks(1).DragOff _
Direction:=xlDown, RegionIndex:=1
ActiveWindow.View = xlNormalView
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$1000"
End Sub