How to enter Page Breaks In a changing range???

E

Ed Lawrence

I need some help! I have an excell speadsheet that each month changes
according to our inventory. Each month I have to manually reset page breaks
and headers as the range grows or shrinks according to inventory in stock
being entered or deleted. How can I arrainge it so this task is done
automaticaly? Any help will be greatly appreciated!

Thanks!
 
F

Frank Kabel

Hi
is there a logic where to insert these page breaks (e.g. after a
specific text, etc.)
 
D

Dave Peterson

If your data is sorted nicely, maybe you can apply Data|subtotals.

There's an option to add page breaks between groups.

If you get more data, remove the data|subtotals, sort the new data range, and
reapply data|subtotals.
 
E

Ed Lawrence

Thanks for the response! My speadsheet has laminate colors that we stock. all
I need is the page breaks and headers to be adjusted so when I add or delete
laminate colors the printed ranges will fall on one page then next page it
continues. There is no numerical sequencing or subtotals, this is just a list
that is updated according to our inventory. Usually, the printed list runs
3-4 pages. Thanks for any help you can give me on this matter, it has plauged
me now for a year!
 
E

Ed Lawrence

Hi Frank,
Yes, I manualy insert page breaks now. What I am wanting is an automatic
page break that will put them in after I update the list. Call me lazy! hahaa!
 
F

Frank Kabel

Hi
should the pagebreaks be inserted every time column A's value changes?.
If yes try the following macros (inserts a pagebreak if the value in
column A changes). Adapt this to your needs

Option Explicit
Sub insert_pagebreak()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "A").Value <> _
Cells(row_index + 1, "A").Value Then
ActiveSheet.HPageBreaks.Add Before:= _
Cells(row_index + 1, "A")
End If
Next
End Sub

Sub remove_them()
ActiveSheet.ResetAllPageBreaks
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Ed Lawrence said:
Hi Frank,
Yes, I manualy insert page breaks now. What I am wanting is an automatic
page break that will put them in after I update the list. Call me lazy! hahaa!
Click to expand...
 
Top