Excel wet dream?

samilynn

Board Regular
Joined
Jun 24, 2003
Messages
168
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi Shaun,
yes, that is exactly what I am trying to do!

Am I asking for too much, or is that possible?
 
Upvote 0
If that is possible..I am not even sure where to start. Sorry :(

Hope the page break thing works for you though
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,760
Messages
6,138,451
Members
450,138
Latest member
NLU_VBA

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
Back
Top