Excel wet dream?

[samilynn]

I've searched high and low, and I'm losing confidence that this could be done:

I want to create an automatic page break at each value change in Column A, and also a page footer that "restarts" at each change in the same column.
For example, I would like a "Page x of y" footer, but I don't want "Page 1 of 3,500", I want "Page 1 of 3", etc...every time that Column A value changes.

Any suggestions?

I'm at a loss, your expertise is my last hope before doing this the manual, time consuming way.

Thank you,

Samantha

 

[ShaunD30]

I can assist (i think) with the auto page break..

Without knowing what your data looks like..lets assume column A contains the values you will base your page breaks on.

Try this code

Sub PageBreakInsert()
Finalrow = Cells(Rows.Count, 1).End(xlUp).Address
Range("A2").Select
CurrentVal = ActiveCell.Value
Do Until ActiveCell.Address = Finalrow
     
    If ActiveCell.Value = CurrentVal Then
    ActiveCell.Offset(1, 0).Select
    Else
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    CurrentVal = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    End If
    Loop
  
End Sub

As far as the footer request...i am not sure ..perhaps someone else can assist?

 

[ShaunD30]

I found this solution on the net for your page numbers...
Go into your print preview and "setup"
Click on "Customer Footer" and type this in

Page &[Page] of &[Pages]

That should give you the "Page x of y" you are looking for

 

[ShaunD30]

I just re-read your post a few times over to make sure i understood your page numbering request and I think I misunderstood.

Are you asking for a way to put page breaks that show the number of pages per unique value in the column?
IE
Joe has 3 pages
Jeff has 2

Joes page footers = "1 of 3", "2 of 3", "3 of 3"
Jeff page footers = "1 of 2", "2 of 2".
and so on and so on...?

Shaun

Shaun

 

[samilynn]

Hi Shaun,
yes, that is exactly what I am trying to do!

Am I asking for too much, or is that possible?

 

[ShaunD30]

If that is possible..I am not even sure where to start. Sorry

Hope the page break thing works for you though

 

[ShaunD30]

I have been thinkin...One thing you could do is use the code above..but add to it

Perhaps creating a range variable for each set of unique values, and having it print each range separately.

Don't have time at the moment to code it..but it might work. Will check back tomorrow .

Shaun

 

[samilynn]

I have to admit that is beyond my current knowledge. I know you're busy, but please see what you can do, it will make the biggest difference in the world!
Thank you,

Samantha

 

[Ragnar1211]

My experience is that the headers and footers are terrible when it comes to customization.

However an alternative would be to place the desired information on the actual page in excel. This may not be what you are after but it may help.

 

[jasonb75]

Maybe a different approach would work.

Instead of setting page breaks by value of column A, select range by value of column A and print selection.