Excel wet dream?

samilynn

Board Regular
Joined
Jun 24, 2003
Messages
137
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

ShaunD30

Board Regular
Joined
Jun 19, 2008
Messages
172
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

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

Board Regular
Joined
Jun 19, 2008
Messages
172
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

Code:
Page &[Page] of &[Pages]

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

ShaunD30

Board Regular
Joined
Jun 19, 2008
Messages
172
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

Board Regular
Joined
Jun 24, 2003
Messages
137

ADVERTISEMENT

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

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

ShaunD30

Board Regular
Joined
Jun 19, 2008
Messages
172
If that is possible..I am not even sure where to start. Sorry :(

Hope the page break thing works for you though
 

ShaunD30

Board Regular
Joined
Jun 19, 2008
Messages
172

ADVERTISEMENT

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

Board Regular
Joined
Jun 24, 2003
Messages
137
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

Well-known Member
Joined
Jul 10, 2008
Messages
571
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,548
Messages
5,596,791
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top