Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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 CheersJulieD"Jared" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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 Befo=Range("A31") End Sub Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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. "Jared" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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 "Jared" wrote in message ... 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. "Jared" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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? "Sharad" wrote: 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 Befo=Range("A31") End Sub Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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 "Jared" wrote in message ... 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? "Sharad" wrote: 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 Befo=Range("A31") End Sub Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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? "JulieD" wrote: 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 "Jared" wrote in message ... 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. "Jared" wrote: 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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? "JulieD" wrote: 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 "Jared" wrote in message ... 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. "Jared" wrote: 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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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" wrote: 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 "Jared" wrote in message ... 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? "Sharad" wrote: 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 Befo=Range("A31") End Sub Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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 "Jared" wrote in message ... 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" wrote: 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 "Jared" wrote in message ... 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? "Sharad" wrote: 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 Befo=Range("A31") End Sub Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded Macro to Set page breaks generates error.
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2003 macro generates compiler error on 2007 | Excel Worksheet Functions | |||
blank row macro with page breaks | Excel Programming | |||
blank row macro with page breaks | Excel Programming | |||
blank row macro with page breaks | Excel Programming | |||
Macro to insert page breaks when values in 1 or more columns changes | Excel Programming |