individual page no for each Subtotal page break section

[arihan1511]

I have a Excel spreadsheet which prepares the payment of visiting doctors. Entire data of a month or within two date range is selected and then Total of each Doctor is prepared using Subtotal function with page breaks at each subtotal.Centre Footer of each page is formatted to give current page no of total no of pages.Hence if there are total 10 page breaks then while printing each page footer displays page 1 of 10,2 of 10,3 of 10, .... 10 of 10.

Logically speaking since each subtotal gives total of individual doctor, the page no of each subtotal break should start with 1 to total no of pages in that page break section Example

while printing first page break with one page in that section the page no should be 1 of 1 , printing second page break with 3 pages in that section the page no should be 1 of 3, 2 of 3, & 3 of 3 ............. till 10th page break is printed .THIS IS POSSIBLE MANNUALY TO SELECT EACH PAGE BREAK AND GIVE PRINT COMMAND but this is lengthy and cumbersome procedure

Hence I require a working code to print the SubTotal data having page breaks with page numbering of each pagebreak section to start from 1 to total no of pages in that section

Forum help in this regard will be highly appreciated

 

[Luke M]

Can we use a macro to loop through the selection process? Something like this might work for you. I had to guess as what text string we were looking for.

[pre]

Sub PrintPages()
Dim startCell As Range
Dim foundCell As Range
Dim firstAddress As String
Dim searchString As String

'What to look for?
searchString = "subtotal"
'What cell is the start of our range?
Set startCell = Range("A1")

Set foundCell = Nothing
Set foundCell = Range("A:A").Find(searchString, startCell)
If foundCell Is Nothing Then Exit Sub 'nothing found
firstAddress = foundCell.Address
Do
Range(startCell, foundCell).EntireRow.PrintOut
Set startCell = foundCell.Offset(1, 0)
Set foundCell = Range("A:A").FindNext(foundCell)
Loop While Not foundCell Is Nothing And foundCell.Address <> firstAddress

End Sub

[/pre]

 

[arihan1511]

Thanks Luke M for the response.IT IS WORKING BEUTIFULLY provided a identical text or no is present in a column. What I am looking for is exel SUBTOTAL formula as a search criteria present in a column

Can you modify the code so that the string searches SUBTOTal formula in Column L.Your effort in this regard will be highly appreciated

 

[Luke M]

Sure thing, just a quick change to the Find method. Note that I changed the search string slightly to help make sure it only picks up the SUBTOTAL function, and not the word "subtotal" in your worksheet. I think that's what you wanted...

Sub PrintPages()
    Dim startCell As Range
    Dim foundCell As Range
    Dim firstAddress As String
    Dim searchString As String
   
    'What to look for?
    searchString = "subtotal("
    'What cell is the start of our range?
    Set startCell = Range("A1")
   
    Set foundCell = Nothing
    Set foundCell = Range("A:A").Find(What:=searchString, After:=startCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If foundCell Is Nothing Then Exit Sub 'nothing found
    firstAddress = foundCell.Address
    Do
        Range(startCell, foundCell).EntireRow.PrintOut
        Set startCell = foundCell.Offset(1, 0)
        Set foundCell = Range("A:A").FindNext(foundCell)
    Loop While Not foundCell Is Nothing And foundCell.Address <> firstAddress
End Sub

 

[arihan1511]

THANKS LUKE M for your effort.You have made it look so simple........ your modified code is my answer and it is working fantastically.

Lastly since I have posted first time on this forum , can u tell me how do I mark this post as SOLVED/CLOSED

 

[Luke M]

Glad I could help out arihan!

Unfortunately, our current forum setup doesn't allow us to mark things SOLVED/CLOSED. It's on the current "Excel Ninja wish list". =)

Typically, I just check it anyone has responded to a thread since the last time I've posted, and that lets me know if there's still an ongoing issue, or if it's closed. I do appreciate you asking though, and feel free to ask another question anytime...on a new thread of course. =P

 

[arihan1511]

Sorry Luke M. I had been out of town.There are no ongoing issues with the code provided by you. This thread can be considered as closed.

THANKS ONCE AGAIN.....