Printing page # of #

[Lighting]

Had to go to work on Sunday to test it out. Couldn't wait till tommorrow.

daleyman, your solution worked, but as some rows got deleted and pagecount changes it still prints 7 pages. I got for example page 7 of 4. So it didn't suite me completly. Thanks anyway.

Jay Petrulis macro was just what I needed. It worked perfectly. Thanks.
Can you please explain what the following means and do?

Dim Pagecount as Integer
Dim PageNumber as Integer
PageCount=ExecuteExcel4Macro("Get.Document(50)")

And I bother you a little more.
Can you please add a code that let me choose how many copies I want to print.
Thanks in advance.

Lighting

[Lighting]

Can someone help me with the following?

I have to print an invoice which can change from 1 to 7 pages. I have set a print range and the rows that are empty I just delete them. By doing this the quantity of pages change. I have some repeated rows that have to print on each page.
Now my problem begins. Somewhere in the repeated rows I have to print page # of #.
I am doing this now by changing the numbers manualy and print each page seperetly. As this is taking allot of time I want to automate this process.
Can someone help me out with a way to do that or a macro to do this.
Thanks in advanced.

Lighting

[daleyman]

before writing a complex macro, may i suggest that you use the "Rows to repeat at Top" to give you the repetition, and settle for just having a page footer with the "Page # of #" embedded in it?

[Lighting]

Hi daleyman,
I can not do that as the invoice is in a pre define format that the company have for some years now. They use to fill this with a type writter.

Lightman

[daleyman]

ah, beautiful tradition.

If i came across this at work I would do a full overhaul of the worksheet and sell it to the managers as being worth the change.

if that aint an option, the vba will really depend on the exact layout of your required solution. feel free to mail me with the workbook if it isn't too sensitive: david@monnetqos.com and i'll review it tomorrow.

[Lighting]

daleyman,

Thanks for your offer.

I ahve just send you the file.

Lighting

[daleyman]

for the benefit of the board, the initial solution looks like this...


Public pagecount As Integer


Sub print_all()
pagecount = 4

'set rows to repeat at top of each page and to fit to single page

ActiveSheet.PageSetup.PrintTitleRows = "$69:$79"
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

'print each page...

print_page 80, 126, 1
print_page 127, 177, 2
print_page 178, 228, 3
print_page 229, 279, 4
print_page 280, 330, 5
print_page 331, 381, 6
print_page 382, 412, 7

End Sub

Sub print_page(startrow, endrow, pagenumber)

'set "page # of #"
Range("S70").Value = pagenumber & " of " & pagecount

'set print area
ActiveSheet.PageSetup.PrintArea = "$M$" & startrow & ":$W$" & endrow

'print page
ActiveWindow.SelectedSheets.PrintOut copies:=1

End Sub




would've liked to have the code work out how many pages and where the breaks should be, but the sheet layout was sporadic, so it would've got messy.

Lightman, if you like send me it again with equal numbers of rows per section and i'll recode it, if you need it to be more flexible.

[Jay Petrulis]

On 2002-04-13 17:48, Lighting wrote:
Can someone help me with the following?

I have to print an invoice which can change from 1 to 7 pages. I have set a print range and the rows that are empty I just delete them. By doing this the quantity of pages change. I have some repeated rows that have to print on each page.
Now my problem begins. Somewhere in the repeated rows I have to print page # of #.
I am doing this now by changing the numbers manualy and print each page seperetly. As this is taking allot of time I want to automate this process.
Can someone help me out with a way to do that or a macro to do this.
Thanks in advanced.

Lighting

Hi,

This puts the Page # of ## in range M4 of the active sheet. Adjust to suit.

---begin VBA---
Sub InsertPageNumData()

Dim Pagecount as Integer
Dim PageNumber as Integer

PageCount = ExecuteExcel4Macro("Get.Document(50)")
For PageNumber = 1 To PageCount
Range("M4") = "Page " & PageNumber & " of " & PageCount
ActiveWindow.SelectedSheets.PrintOut From:=PageNumber, To:=PageNumber
Next PageNumber

End Sub
---end VBA---

HTH,
Jay

[Lighting]

Thanks for the solutions.
I'll test them Monday at work when I have the full access to all data and let you know how it turned out.
Lighting

[Lighting]

Bumb

Don't see my post after last reply.