|
Page break macro
i don't know the correct syntax (which you might be able to get from
those other posts you read), but how 'bout start @ row 1 go down (what? 50 rows?) if row is empty, insert page break if row is not empty, then range.end(xlup) to beginning of area/group. offset up once more, & that should be your first blank line previous to the expected page break. if row is empty, insert page break. go down 50 rows again, etc. yes, it looks like it would all have to be based on column c. susan On Jan 24, 1:35 pm, liz25mc wrote: Hello! I've read and reread the page break posts and can't find what I need. I have a spreadsheet list of items that are split into groups ie: Column A Column B Column C 5 1234-566 Brown 5 Brown 5 Brown Then a blank row 5 1235-555 Green 5 Green 5 Green Then a blank row etc. There might not always be three rows before the blank. I need a macro that will add a page break but not break up the set of three if it falls at the end of the sheet. I am thinking the page break will need to be based on Column C, somehow, but I am not sure how to go about it. Any help would be appreciated. Thanks. |
Page break macro
:) i think a combination of rob's code & my ideas would work........ i still think if you go down 50 rows & find a not-empty row, moving end(xlup) plus one offset up would take you to the first empty row before the 50 pages, thereby not breaking up any of your groupings. thinking about it more, you would also have to start with a lastrow (go to row 20000 & xlup to find out the last row with info on it, so you'd know when to stop going down 50 more rows). i love excel & i can do it, but not quickly! it would take me at least a couple of hours to code this; rob seem more compentent & able to do it more quickly, but i'll start trying & maybe by tomorrow morning i'd have something for you??? :) susan On Jan 24, 2:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz |
Page break macro
You said that there might not always be 3 rows before the blank, so
what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz |
Page break macro
hey! i got it to work already! :)
different style than rob's............. Option Explicit Sub pgbrks() Dim MyRange As Variant Dim LastCellRow As Long Dim StartPoint As Range LastCellRow = Sheets("Sheet1").Cells(20000, 1).End(xlUp).Row Set MyRange = Range("a1:c" & LastCellRow) Set StartPoint = Range("c1") StartPoint.Select ActiveCell.Offset(50, 0).Select Do While ActiveCell.Row <= LastCellRow If ActiveCell.Value = "" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ElseIf ActiveCell.End(xlUp).Offset(-1, 0).Select Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell End If ActiveCell.Offset(51, 0).Select Loop StartPoint.Select End Sub xxxxxxxxxxxxxxxxxxxxxxxx it's not as "pretty" as some would have it, and i am making the beginner "mistake" of using .select, but hey, it works! refining it WOULD take until tomorrow morning (for me)! caution - has not been tested extensively - there may be bugs. susan On Jan 24, 3:06 pm, "Susan" wrote: :) i think a combination of rob's code & my ideas would work........ i still think if you go down 50 rows & find a not-empty row, moving end(xlup) plus one offset up would take you to the first empty row before the 50 pages, thereby not breaking up any of your groupings. thinking about it more, you would also have to start with a lastrow (go to row 20000 & xlup to find out the last row with info on it, so you'd know when to stop going down 50 more rows). i love excel & i can do it, but not quickly! it would take me at least a couple of hours to code this; rob seem more compentent & able to do it more quickly, but i'll start trying & maybe by tomorrow morning i'd have something for you??? :) susan On Jan 24, 2:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text - |
Page break macro
OK, meeting cancelled... :) love those!
Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text - |
Page break macro
sorry, you don't need to dim y.
I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
rob -
yes, i did use 50 rows cuz that's what fit my printer; i assumed liz would have to ammend that to fit her printer & margins, etc., especially if the pages are landscape orientation. based on liz's sample, it seems to me you HAVE to run this based on column c, because there's so many blanks under the numeric cells - only the color cell is repeated down the grouping. mine didn't set a print area, just set the page breaks, which get re-set each time you run the macro. i didn't do 30 pages of tests, only about 5 pages of random lengths of groups. and your coding is better because you avoided those nasty "selects". it would have taken me a lot longer to code it that correct way. whichever code works, that's what's important! :) nice "working" with you! susan ps to liz - we macro-guru-wanna-be's don't mind working on them at home - we like it!!!! ha ha On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
i just tried to run yours to see how it worked & it says
that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
Thanks Susan and Rob,
I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
liz - are any of the groups simply one line? that might account for the weirdness on mine..... also, try this first......... on page break preview, right click & reset all page breaks. see what row # the first page break breaks on naturally. (mine was row 51). if yours is different due to page length and/or or- ientation and/or margins, adjust the code accordingly. then see what it does. susan On Jan 25, 9:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
OK, this should fix errors in the code.
First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
In my code, this:
Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) Should be: Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) check the code because google likes to word wrap. if it shows up as red in excel vba, just combine the lines... Rob On Jan 25, 8:50 am, "okrob" wrote: OK, this should fix errors in the code. First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
it still says HPageBreak is an undefined variable.
this is so funny - two completely different codes floating around & we can't make either of them work!! some guru is probably reading this post with great amusement, waiting to see if we'll figure it out & make it work, or give up! ha ha susan On Jan 25, 9:50 am, "okrob" wrote: OK, this should fix errors in the code. First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
Yes, you're right, some of the groups are two or one row instead of three.
It's many pages long - came from someone else - and I wasn't aware there were different row counts per grouping. I apologize for missing that. Otherwise, it looks good. Where to from here? "Susan" wrote: liz - are any of the groups simply one line? that might account for the weirdness on mine..... also, try this first......... on page break preview, right click & reset all page breaks. see what row # the first page break breaks on naturally. (mine was row 51). if yours is different due to page length and/or or- ientation and/or margins, adjust the code accordingly. then see what it does. susan On Jan 25, 9:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
i have 2 meetings i have to go to now, so i won't be back till around lunchtime...... poor liz - i hope one of these codes starts to work for you!!! susan On Jan 25, 9:56 am, "Susan" wrote: it still says HPageBreak is an undefined variable. this is so funny - two completely different codes floating around & we can't make either of them work!! some guru is probably reading this post with great amusement, waiting to see if we'll figure it out & make it work, or give up! ha ha susan On Jan 25, 9:50 am, "okrob" wrote: OK, this should fix errors in the code. First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
Hi Rob,
Still getting the sub-script out of range error on the same line of code: For Each HPageBreak In ActiveSheet.HPageBreaks Also says (on mouse over) HPageBreak = Nothing Thanks. Liz "okrob" wrote: OK, this should fix errors in the code. First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
i hate one-row groups..... i find them very hard to work around.
because if you end(xlup) you'll end up at the bottom of the previous group, not the top of that one, because you're already at the top & don't know it. the way i've worked around it before is to add an offset DOWN one row to see if the next row is blank. if it is, then offset UP one row & see if that one is blank, too. then i know it's a one-row group & can set the page break directly above that line. it takes more IF coding but as i said i wouldn't be able to come back to work on it till afternoon. i'm sorry we're not helping you as quickly as we'd hoped! susan On Jan 25, 10:00 am, liz25mc wrote: Yes, you're right, some of the groups are two or one row instead of three. It's many pages long - came from someone else - and I wasn't aware there were different row counts per grouping. I apologize for missing that. Otherwise, it looks good. Where to from here? "Susan" wrote: liz - are any of the groups simply one line? that might account for the weirdness on mine..... also, try this first......... on page break preview, right click & reset all page breaks. see what row # the first page break breaks on naturally. (mine was row 51). if yours is different due to page length and/or or- ientation and/or margins, adjust the code accordingly. then see what it does. susan On Jan 25, 9:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
Hi Rob,
Yes, I did have all the little things like the word wrapping fixed..still no go. I have some experience with vb, just not enough to do this particular thing on my own. Thanks. "okrob" wrote: In my code, this: Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) Should be: Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) check the code because google likes to word wrap. if it shows up as red in excel vba, just combine the lines... Rob On Jan 25, 8:50 am, "okrob" wrote: OK, this should fix errors in the code. First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
What version of office/excel?
it's used to define variables, not one itself. search this group on HPageBreaks to find out. I'm not sure what's happening. Rob BTW, I'm using O2k3. On Jan 25, 8:56 am, "Susan" wrote: it still says HPageBreak is an undefined variable. this is so funny - two completely different codes floating around & we can't make either of them work!! some guru is probably reading this post with great amusement, waiting to see if we'll figure it out & make it work, or give up! ha ha susan On Jan 25, 9:50 am, "okrob" wrote: OK, this should fix errors in the code. First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
i have excel 2000
which apparently doesn't recognize HPageBreak as something unique to excel. susan, waiting for one more person to arrive for the meeting..... On Jan 25, 10:11 am, "okrob" wrote: What version of office/excel? it's used to define variables, not one itself. search this group on HPageBreaks to find out. I'm not sure what's happening. Rob BTW, I'm using O2k3. On Jan 25, 8:56 am, "Susan" wrote: it still says HPageBreak is an undefined variable. this is so funny - two completely different codes floating around & we can't make either of them work!! some guru is probably reading this post with great amusement, waiting to see if we'll figure it out & make it work, or give up! ha ha susan On Jan 25, 9:50 am, "okrob" wrote: OK, this should fix errors in the code. First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
I have Excel 2003.
I'm doing some research on the Microsoft Knowledge Base as well. Appreciate all your help. Worse comes to worse they will have to manually insert page breaks for now. "liz25mc" wrote: Hi Rob, Still getting the sub-script out of range error on the same line of code: For Each HPageBreak In ActiveSheet.HPageBreaks Also says (on mouse over) HPageBreak = Nothing Thanks. Liz "okrob" wrote: OK, this should fix errors in the code. First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
any chance you'd send me about 100 lines of your data so I can see
"exactly" what I'm working with? I know that sometimes that's not possible, (sensitive data and such) but if it is, and you can, email it to me please. I'll check it out and see if it's the code not properly working with your data. Perhaps I 'really' didn't get it when you gave sample data... Rob On Jan 25, 9:42 am, liz25mc wrote: I have Excel 2003. I'm doing some research on the Microsoft Knowledge Base as well. Appreciate all your help. Worse comes to worse they will have to manually insert page breaks for now. "liz25mc" wrote: Hi Rob, Still getting the sub-script out of range error on the same line of code: For Each HPageBreak In ActiveSheet.HPageBreaks Also says (on mouse over) HPageBreak = Nothing Thanks. Liz "okrob" wrote: OK, this should fix errors in the code. First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
|
Page break macro
you should now have my email address...
On Jan 25, 10:16 am, liz25mc wrote: Hi Rob, I can't send you the exact data, but I can provide a spreadsheet in the same format that contains "nonsense" Please provide your email address to: Thanks. "okrob" wrote: any chance you'd send me about 100 lines of your data so I can see "exactly" what I'm working with? I know that sometimes that's not possible, (sensitive data and such) but if it is, and you can, email it to me please. I'll check it out and see if it's the code not properly working with your data. Perhaps I 'really' didn't get it when you gave sample data... Rob On Jan 25, 9:42 am, liz25mc wrote: I have Excel 2003. I'm doing some research on the Microsoft Knowledge Base as well. Appreciate all your help. Worse comes to worse they will have to manually insert page breaks for now. "liz25mc" wrote: Hi Rob, Still getting the sub-script out of range error on the same line of code: For Each HPageBreak In ActiveSheet.HPageBreaks Also says (on mouse over) HPageBreak = Nothing Thanks. Liz "okrob" wrote: OK, this should fix errors in the code. First, it reset's the sheet's pagebreaks to all automatic. Then it starts the process of setting pagebreaks where they should be. Had trouble getting it to loop through enough times, and also fixed it so that it's working off column C now. Did this with 42 pages of data and it worked. (ended up with about 50+ pages after moving the breaks) Sub pgbrks() Application.ScreenUpdating = False ActiveSheet.ResetAllPageBreaks Dim x As String Dim y As String Dim i As Integer Dim z As Integer Dim j As Integer z = ActiveSheet.HPageBreaks.Count z = z / 2 For i = 1 To z Step 1 For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Set HPageBreak.Location = HPageBreak.Location.Offset(1, 0) Next HPageBreak Next i Application.ScreenUpdating = True End Sub Rob On Jan 25, 8:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
ok, liz, here's my next attempt.
in testing it i ran across another problem that i've addressed. it works for me through 500 rows filled with groups of varying amounts (including the dreaded one-row-group). works ok for me. i hope it works for you! as rob had noted above, watch out for google-line-wrapping - if any code turns red when pasting it in the module, backspace it to the line above, where it really belongs. let me know if it doesn't work & i'll try to address whatever glitch it comes up with. thanks for letting me play with your problem so much to try to come up with a solution! i appreciate your patience with my lack of guru-ness. susan xxxxxxxxxxxxxxxxxxxxxxxxxx Sub pgbrks1() 'written for newsgroup on 01/25/07 by Susan Dim MyRange As Variant Dim LastCellRow As Long Dim StartPoint As Range ActiveSheet.ResetAllPageBreaks LastCellRow = Sheets("Sheet1").Cells(20000, 1).End(xlUp).Row Set MyRange = Range("a1:c" & LastCellRow) Set StartPoint = Range("c1") StartPoint.Select ActiveCell.Offset(50, 0).Select Do While ActiveCell.Row <= LastCellRow If ActiveCell.Value = "" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ElseIf ActiveCell.Offset(-1, 0).Value = "" And _ ActiveCell.Offset(1, 0).Value = "" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ElseIf ActiveCell.End(xlUp).Offset(-1, 0).Select Then If ActiveCell.Value = "" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ElseIf ActiveCell.End(xlDown).Offset(1, 0).Select Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell End If End If ActiveCell.Offset(51, 0).Select Loop StartPoint.Select End Sub xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx On Jan 25, 9:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
After seeing the actual spreadsheet, I see now why we haven't really
gotten anywhere with this... First, the option explicit statement will cause my macro to throw a variable error as you found when trying my solution out. Second, she has more than one issue here... One, if she resets all pagebreaks, she will split her data up vertically and horizontally... So, I had to account for the vertical pagebreak move by using drag to the right. Then run the thing. Also, my macro only works while in the pagebreak preview, so I had to show both switches from normal to pbp and back after the macro was run. It's transparent as I turned off screen updating. But it's still quite long running depending on the amount of data on the page and how many times it has to loop through. Unfortunately some of these things you learn along the way. Hopefully liz will have taken what both of us have put here and put some of it to good use. I'll post my code tomorrow when I get back to work. (forgot to bring my flash drive home) I'll also post a more enlightening form of her data (nothing sensitive) to reveal why we were having problems. Ciao Rob On Jan 25, 12:57 pm, "Susan" wrote: ok, liz, here's my next attempt. in testing it i ran across another problem that i've addressed. it works for me through 500 rows filled with groups of varying amounts (including the dreaded one-row-group). works ok for me. i hope it works for you! as rob had noted above, watch out for google-line-wrapping - if any code turns red when pasting it in the module, backspace it to the line above, where it really belongs. let me know if it doesn't work & i'll try to address whatever glitch it comes up with. thanks for letting me play with your problem so much to try to come up with a solution! i appreciate your patience with my lack of guru-ness. susan xxxxxxxxxxxxxxxxxxxxxxxxxx Sub pgbrks1() 'written for newsgroup on 01/25/07 by Susan Dim MyRange As Variant Dim LastCellRow As Long Dim StartPoint As Range ActiveSheet.ResetAllPageBreaks LastCellRow = Sheets("Sheet1").Cells(20000, 1).End(xlUp).Row Set MyRange = Range("a1:c" & LastCellRow) Set StartPoint = Range("c1") StartPoint.Select ActiveCell.Offset(50, 0).Select Do While ActiveCell.Row <= LastCellRow If ActiveCell.Value = "" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ElseIf ActiveCell.Offset(-1, 0).Value = "" And _ ActiveCell.Offset(1, 0).Value = "" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ElseIf ActiveCell.End(xlUp).Offset(-1, 0).Select Then If ActiveCell.Value = "" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ElseIf ActiveCell.End(xlDown).Offset(1, 0).Select Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell End If End If ActiveCell.Offset(51, 0).Select Loop StartPoint.Select End Sub xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx On Jan 25, 9:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
ahhhhhh. well, it was still a good learning exercise for me, anyway. :) susan On Jan 25, 11:09 pm, "okrob" wrote: After seeing the actual spreadsheet, I see now why we haven't really gotten anywhere with this... First, the option explicit statement will cause my macro to throw a variable error as you found when trying my solution out. Second, she has more than one issue here... One, if she resets all pagebreaks, she will split her data up vertically and horizontally... So, I had to account for the vertical pagebreak move by using drag to the right. Then run the thing. Also, my macro only works while in the pagebreak preview, so I had to show both switches from normal to pbp and back after the macro was run. It's transparent as I turned off screen updating. But it's still quite long running depending on the amount of data on the page and how many times it has to loop through. Unfortunately some of these things you learn along the way. Hopefully liz will have taken what both of us have put here and put some of it to good use. I'll post my code tomorrow when I get back to work. (forgot to bring my flash drive home) I'll also post a more enlightening form of her data (nothing sensitive) to reveal why we were having problems. Ciao Rob On Jan 25, 12:57 pm, "Susan" wrote: ok, liz, here's my next attempt. in testing it i ran across another problem that i've addressed. it works for me through 500 rows filled with groups of varying amounts (including the dreaded one-row-group). works ok for me. i hope it works for you! as rob had noted above, watch out for google-line-wrapping - if any code turns red when pasting it in the module, backspace it to the line above, where it really belongs. let me know if it doesn't work & i'll try to address whatever glitch it comes up with. thanks for letting me play with your problem so much to try to come up with a solution! i appreciate your patience with my lack of guru-ness. susan xxxxxxxxxxxxxxxxxxxxxxxxxx Sub pgbrks1() 'written for newsgroup on 01/25/07 by Susan Dim MyRange As Variant Dim LastCellRow As Long Dim StartPoint As Range ActiveSheet.ResetAllPageBreaks LastCellRow = Sheets("Sheet1").Cells(20000, 1).End(xlUp).Row Set MyRange = Range("a1:c" & LastCellRow) Set StartPoint = Range("c1") StartPoint.Select ActiveCell.Offset(50, 0).Select Do While ActiveCell.Row <= LastCellRow If ActiveCell.Value = "" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ElseIf ActiveCell.Offset(-1, 0).Value = "" And _ ActiveCell.Offset(1, 0).Value = "" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ElseIf ActiveCell.End(xlUp).Offset(-1, 0).Select Then If ActiveCell.Value = "" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ElseIf ActiveCell.End(xlDown).Offset(1, 0).Select Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell End If End If ActiveCell.Offset(51, 0).Select Loop StartPoint.Select End Sub xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx On Jan 25, 9:35 am, liz25mc wrote: Thanks Susan and Rob, I have tried both macros and come up with different errors for each one. Rob's coding I get the Sub-script out range error on the following line of code: For Each HPageBreak In ActiveSheet.HPageBreaks I also get the Page Break = Empty when I mouse over the code line-per Susan's post. On Susan's the code runs, but I get breaks between groups of three. The first page will have 50 rows, the next 1 row, the next 50, the next 3...doesn't seem to be a pattern. So! I'm learning lots (I'm ok at vb, but this is over my abilities)..I've played around with both sets and have reviewed "fixes" in the Microsoft Knowledge base but no luck! Any other help is appreciated! Thanks. "Susan" wrote: ok, well it worked from the standpoint that the code compiled & ran........... but i had it on page break view to see it change as i stepped thru it. the code ran fine but it didn't change any of the page breaks. the page breaks were not hard-coded, but i had reset them all to automatic to see it reset them....... the mouse-over says HPageBreak = nothing. susan On Jan 25, 8:23 am, "Susan" wrote: i just tried to run yours to see how it worked & it says that the variable HPageBreak is not defined. i added Dim HPageBreak as Object and that worked. susan On Jan 24, 5:08 pm, "okrob" wrote: sorry, you don't need to dim y. I was playing around with something else at the same time. ALSO, I noticed that I based this on Column A I *think* it can be based on column C, but based on your sample data, I don't see the need. Rob On Jan 24, 4:05 pm, "okrob" wrote: OK, meeting cancelled... :) love those! Susan, Your code worked perfectly...EXCEPT... You used 50 as the hard coded # of cells / page correct? I fiddled around with a version that will take into consideration a taller cell / different printer / different page layout, etc... I tried it on about 30 pages of data. Sub pgbrks() Dim x As String Dim y As String Dim i As Integer i = 0 For i = 1 To 2 Step 1 ' had to loop through the whole thing twice to get all of em rounded up!!! For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location < "" x = HPageBreak.Location.Value If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak Next End Sub Rob On Jan 24, 3:23 pm, liz25mc wrote: Hi Rob, That sounds right, however I don't want to set a print area because it won't always be the same. I really appreciate your offer to work on it at home, however, please don't feel that you need to do that. Thanks again to you and Susan. Thought I would try her code first thing in the am as I am on my way home now. Liz "okrob" wrote: You said that there might not always be 3 rows before the blank, so what you're after is a macro that after the print area is set, it checks the page breaks to see if the row below (or above?) is blank. If not, xlup to a blank row and insert the page break. Loop this until all page breaks are above(below) a blank row... The above or below question needs answered and I'll work on it tonight. I've got meetings all afternoon here at work, but can start this evening at home. Rob On Jan 24, 1:57 pm, liz25mc wrote: Hi Rob and Susan, Thanks for your input. You're correct, I didn't make what I am after clear. I need to have whatever number of rows per page (say 50..per Susan) but without a page break falling within my group of three. Rob, I tried your code and it worked great, except it put a page break after each group of three...I need to have the page filled and then go to the next page without any breaks falling into my group of three. Any ideas? Thanks in advance. Liz- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Page break macro
Good morning,
Thanks again Rob and Susan for all your help! I hope you know how much I have appreciated you going above and beyond! I had to make a few minor changes to Rob's code to get it to work....what I used following in this post. It's working great now, except (of course there's an except!) I only get six or so groups of three on each page, so each page does not fill to the bottom. There will be 5 or 6 pages like that and then 1 that fills up with 12 groups of three. Any ideas about that? Otherwise, it works great. Here is the code (I had to comment out the VPageBreak so had to add the formatting back in at the end) Sub pgbrks() ActiveWindow.View = xlPageBreakPreview ActiveSheet.ResetAllPageBreaks 'ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 Dim x As String For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak ActiveWindow.View = xlNormalView With ActiveSheet.PageSetup .PrintTitleRows = "$8:$8" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 .PrintErrors = xlPrintErrorsDisplayed End With Application.ScreenUpdating = True End Sub |
Page break macro
That's more to do with the way you did the page setup after the code
runs than anything. Try resetting all pagebreaks, drag the one to the right, then run the portion for page setup, then the meat and potatoes of the pgbrk code. Do all of this while in pagebreak preview, then come out of it at the end. This should fix the 'exception' you have. But, of course, I haven't actually tried it. I really should bring my laptop to work so I have all the stuff here that I do at home and vice versa... :0) Rob On Jan 26, 10:03 am, liz25mc wrote: Good morning, Thanks again Rob and Susan for all your help! I hope you know how much I have appreciated you going above and beyond! I had to make a few minor changes to Rob's code to get it to work....what I used following in this post. It's working great now, except (of course there's an except!) I only get six or so groups of three on each page, so each page does not fill to the bottom. There will be 5 or 6 pages like that and then 1 that fills up with 12 groups of three. Any ideas about that? Otherwise, it works great. Here is the code (I had to comment out the VPageBreak so had to add the formatting back in at the end) Sub pgbrks() ActiveWindow.View = xlPageBreakPreview ActiveSheet.ResetAllPageBreaks 'ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 Dim x As String For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak ActiveWindow.View = xlNormalView With ActiveSheet.PageSetup .PrintTitleRows = "$8:$8" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 .PrintErrors = xlPrintErrorsDisplayed End With Application.ScreenUpdating = True End Sub |
Page break macro
Hi Rob,
I had to comment out the VPageBreak because I was getting a Subscript out of range error with it in. Did some research on the Knowledge Base website and, although the example there wasn't exactly the same as my situation, commenting it out got rid of the subcript error and allowed the code to run. Just that 1 little exception. Because I had to comment out the VPageBreak, I then had to add the page setup back in. And I also got the same results (number of rows per page) without the page setup info included in the code. Quite an ordeal for something that would seem so simple. "okrob" wrote: That's more to do with the way you did the page setup after the code runs than anything. Try resetting all pagebreaks, drag the one to the right, then run the portion for page setup, then the meat and potatoes of the pgbrk code. Do all of this while in pagebreak preview, then come out of it at the end. This should fix the 'exception' you have. But, of course, I haven't actually tried it. I really should bring my laptop to work so I have all the stuff here that I do at home and vice versa... :0) Rob On Jan 26, 10:03 am, liz25mc wrote: Good morning, Thanks again Rob and Susan for all your help! I hope you know how much I have appreciated you going above and beyond! I had to make a few minor changes to Rob's code to get it to work....what I used following in this post. It's working great now, except (of course there's an except!) I only get six or so groups of three on each page, so each page does not fill to the bottom. There will be 5 or 6 pages like that and then 1 that fills up with 12 groups of three. Any ideas about that? Otherwise, it works great. Here is the code (I had to comment out the VPageBreak so had to add the formatting back in at the end) Sub pgbrks() ActiveWindow.View = xlPageBreakPreview ActiveSheet.ResetAllPageBreaks 'ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 Dim x As String For Each HPageBreak In ActiveSheet.HPageBreaks Do While HPageBreak.Location.Offset(0, 2) < "" x = HPageBreak.Location.Offset(0, 2) If x = "" Then 'do nothing Else Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) End If Loop Next HPageBreak ActiveWindow.View = xlNormalView With ActiveSheet.PageSetup .PrintTitleRows = "$8:$8" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 .PrintErrors = xlPrintErrorsDisplayed End With Application.ScreenUpdating = True End Sub |
Page break macro
Quite an ordeal for something that would seem so simple.
yes, but wasn't it interesting??? :) & now next time a person is trying to do the same thing, here it is all nicely archived for them. susan On Jan 26, 11:56�am, liz25mc wrote: Hi Rob, I had to comment out the VPageBreak because I was getting a Subscript out of range error with it in. *Did some research on the Knowledge Base website and, although the example there wasn't exactly the same as my situation, commenting it out got rid of the subcript error and allowed the code to run. * Just that 1 little exception. *Because I had to comment out the VPageBreak, I then had to add the page setup back in. And I also got the same results (number of rows per page) without the page setup info included in the code. Quite an ordeal for something that would seem so simple. "okrob" wrote: That's more to do with the way you did the page setup after the code runs than anything. *Try resetting all pagebreaks, drag the one to the right, then run the portion for page setup, then the meat and potatoes of the pgbrk code. *Do all of this while in pagebreak preview, then come out of it at the end. This should fix the 'exception' you have. *But, of course, I haven't actually tried it. *I really should bring my laptop to work so I have all the stuff here that I do at home and vice versa... :0) Rob On Jan 26, 10:03 am, liz25mc wrote: Good morning, Thanks again Rob and Susan for all your help! *I hope you know how much I have appreciated you going above and beyond! I had to make a few minor changes to Rob's code to get it to work....what I used following in this post. *It's working great now, except (of course there's an except!) I only get six or so groups of three on each page, so each page does not fill to the bottom. *There will be 5 or 6 pages like that and then 1 that fills up with 12 groups of three. *Any ideas about that? * Otherwise, it works great. Here is the code (I had to comment out the VPageBreak so had to add the formatting back in at the end) Sub pgbrks() * * *ActiveWindow.View = xlPageBreakPreview * * ActiveSheet.ResetAllPageBreaks * * 'ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 * * Dim x As String * * * * For Each HPageBreak In ActiveSheet.HPageBreaks * * * * * * Do While HPageBreak.Location.Offset(0, 2) < "" * * * * * * * * x = HPageBreak.Location.Offset(0, 2) * * * * * * * * If x = "" Then * * * * * * * * * * 'do nothing * * * * * * * * Else * * * * * * * * * * Set HPageBreak.Location = HPageBreak.Location.Offset(-1, 0) * * * * * * * * End If * * * * * * Loop * * * * Next HPageBreak * * ActiveWindow.View = xlNormalView * * With ActiveSheet.PageSetup * * * * .PrintTitleRows = "$8:$8" * * * * .PrintTitleColumns = "" * * End With * * ActiveSheet.PageSetup.PrintArea = "" * * With ActiveSheet.PageSetup * * * * .LeftHeader = "" * * * * .CenterHeader = "" * * * * .RightHeader = "" * * * * .LeftFooter = "" * * * * .CenterFooter = "" * * * * .RightFooter = "" * * * * .LeftMargin = Application.InchesToPoints(0.25) * * * * .RightMargin = Application.InchesToPoints(0.25) * * * * .TopMargin = Application.InchesToPoints(1) * * * * .BottomMargin = Application.InchesToPoints(1) * * * * .HeaderMargin = Application.InchesToPoints(0.5) * * * * .FooterMargin = Application.InchesToPoints(0.5) * * * * .PrintHeadings = False * * * * .PrintGridlines = True * * * * .PrintComments = xlPrintNoComments * * * * .PrintQuality = 600 * * * * .CenterHorizontally = False * * * * .CenterVertically = False * * * * .Orientation = xlLandscape * * * * .Draft = False * * * * .PaperSize = xlPaperLetter * * * * .FirstPageNumber = xlAutomatic * * * * .Order = xlDownThenOver * * * * .BlackAndWhite = False * * * * .Zoom = 73 * * * * .PrintErrors = xlPrintErrorsDisplayed * * End With * * Application.ScreenUpdating = True End Sub- Hide quoted text -- Show quoted text - |
| All times are GMT +1. The time now is 12:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com