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

 

[jasonb75]

Try this code in a COPY of your workbook, run it with the sheet to print from selected.

The code as written is setup to display a messagebox telling you what will be printed, each time you click ok it will move to the next print range / area. I've done it this way so that you can see if it's going to print the correct areas without actually printing. Code is set up to print from columns A to F so may need changing.

Once you're sure that the print areas will be correct deleting the ' in front of Execute should print a hard copy to your default printer.

The message box code line above this can be left in or deleted if desired.

Sub printtest()
    b = 1
For a = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    c = a + 1
If Range("A" & a).Value <> Range("A" & c).Value Then
    Range("A" & b & ":F" & a).Select
MsgBox "Range A" & b & ":F" & a & "Will be printed"
'ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)"
    b = a + 1
End If
Next
End Sub

Hope this helps

 

[ShaunD30]

This is very ugly code..but it does what I think you need it to do..The only thing is that without knowing how many columns of data you want printed, I wasn't sure where to end the print area..This code assumes the furthest column over is column H

Sub PrintPageNum()
Dim Startadd As String, Endadd As String
'Find Final row address
FinalRow = Cells(Rows.Count, 1).End(xlUp).Address
Range("A2").Select
'Name intitial Variables
CurrentVal = ActiveCell.Value
StartPrint = ActiveCell.Address
'Range loop
Do Until ActiveCell.Address = FinalRow
     
    If ActiveCell.Value = CurrentVal Then
        ActiveCell.Offset(1, 0).Select
        
    Else
        ActiveCell.Offset(-1, 7).Select
        
'Name end print variable
    EdnPrint = ActiveCell.Address
        
        ActiveCell.Offset(1, -7).Select
'Set footer
        
    ActiveSheet.PageSetup.PrintArea = StartPrint & ":" & EdnPrint
    
    
    With ActiveSheet.PageSetup
        
        .LeftFooter = "Page &P of &N"
        
    End With
    Sheets("Sheet1").PrintOut
    CurrentVal = ActiveCell.Value
    StartPrint = ActiveCell.Address
    
    ActiveCell.Offset(1, 0).Select
    End If
    Loop
  
  
  'If activecell has reached final row
  
     If ActiveCell.Address = FinalRow Then
         ActiveCell.Offset(0, 7).Select
         EdnPrint = ActiveCell.Address
         ActiveCell.Offset(1, -7).Select
        
     ActiveSheet.PageSetup.PrintArea = StartPrint & ":" & EdnPrint
    
    
    With ActiveSheet.PageSetup
        
        .LeftFooter = "Page &P of &N"
        
    End With
    Sheets("Sheet1").PrintOut
    End If
    
End Sub

Again...VBA Noob=Slow & Ugly code...but it works.

Shaun

 

[ShaunD30]

Sorry..should have looked to see if another solution was posted already.



Shaun

 

[samilynn]

Jason,
your code works PERFECTLY!

Thank you so much, you are incredible.

Thanks again,

sam

 

[Ragnar1211]

It is too bad the title of this post does not reveal the true nature of the problem, and the excellent answer.

I wonder if a moderator could rename the thread, with the original poster's permission, to something like:

"Customize footers for multiple print jobs in the same sheet"


-- Just a thought since I think the answer is pretty helpful for others.


Gerry

 

[samilynn]

Hi Gerry
I tried to rename it myself, but I can't figure out how to do it. If a moderator reads this, please follow Gerry's suggestion, as he is right, this might be helpful to others.

Thanks,

sam

 

[Peter_SSs]

Hi Gerry
I tried to rename it myself, but I can't figure out how to do it. If a moderator reads this, please follow Gerry's suggestion, as he is right, this might be helpful to others.

Thanks,

sam

You can only modify your post for 10 minutes after posting.

If you would like the thread re-named, try clicking the 'Report' button at the top right of your original post and give details of in the text box that becomes available at that time.

I cannot guarantee that the moderator will do the re-name, but that is one way to ask.

 

[ShaunD30]

Jason,

Gotta hand it to ya..Your code is great..I've tried it on quite a few files now..going to come in handy.

Shaun

 

[jasonb75]

Beginners luck Shaun,

The loop code was something I came up with when I first started with VBA, simply to understand how a For - Next loop works, somehow it seemed to work well, now it's simply a case of adding a line to make it useful, in this case to run the print, which came from the macro recorder.

The nice part is, with a couple of simple changes, it works with just about anything, I've used it for cell format changes, adding named ranges, deleting rows, etc.

Simple but effective