Print all pages

[bacon]

Good morning

At present I have over 250 clients that I need to invoice on a monthly basis via Excel.

I am currently having to print each one manually by selecting the Invoice Number (Bill Example Cell E10). This is taking me ages.

Any ideas how I can simplify this arduous task. Ideally I would like a pop up box that says print all.

Thank you

Iain

[XLGibbs]

bacon, haven't forgot about ya... I will be taking a look shortly, will have a potential solution for you in a little while..

[bacon]

You are a superstar.. thank you

[XLGibbs]

Okay Bacon, I confused. Your sheets are predominantly blank....am I missing something?

It sounds like you want to have a button to cycle through the invoices and print them one by one...correct?

Where is the list of invoice numbers that you select....and the bill example..what is the print area?

It would be failry easy to cycle through a list of invoice numbers, update the billing sheet and print it, then go on to the next invoice in list...if that is what you want..I just don't know what is what in your sample..

[bacon]

Ok i am going to reload the spreadsheet as I uploaded it without saving which is why it is confusing...


In the sheet Called Bill Example I need cell D10 to look at Column A in sheet 1 and cycle through all the invoice numbers and print them out.

If I was doing this manually I would type in cell D10 "1" then press print, then I would type in "3" and then press print etc etc

Is that a bit clearer..

sorry for the confusion...

[bacon]

the print area in Bill example is B2:H52...

[Norie]

bacon

I notice there's a link to MS Access.

Could this all not be done there using a report?

[bacon]

bacon

I notice there's a link to MS Access.

Could this all not be done there using a report?

more than likely... but would like to keep it separate from access if possible..

[Norie]

This might be a stupid question, but why?

[bacon]

not a stupid question... the guy that developed the Access database has left and no one knows how to make those kind of changes...

[Norie]

No need for changing the database, if that's what you mean.

Just create a report.

And Access has various wizards for that.

[bacon]

No need for changing the database, if that's what you mean.

Just create a report.

And Access has various wizards for that.

understood but the excel spreadsheet does extra calculations on the data.. I am sure it can be done in Access quite simply and maybe this will be a phrase 2 type project...

[XLGibbs]

Try this out Bacon.

In the attached file I assigned a button outside the invoice area to fire the code..

[VBA]
Sub PrintALLInvoices()
Dim rngID As Range, c As Range
Dim lRow As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rngID = .Range("A2:A" & lRow)
End With

With Sheets("Bill Example ")

For Each c In rngID
Range("B10") = c

.PageSetup.PrintArea = "$B$2:$H$52"
.PrintOut
Next c
End With
Application.ScreenUpdating = True
Set c = nothing, rngID = nothing

End Sub
[/VBA]

You will need to modify the sheet names as appropriate for your live file..

[geekgirlau]

Bacon, are you able to post a scaled-down version of the database? Strip out anything non-relevant to the invoices and just put a few dummy records in it (nothing confidential) and we may be able to help with the report.

[bacon]

Try this out Bacon.

In the attached file I assigned a button outside the invoice area to fire the code..

[VBA]
Sub PrintALLInvoices()
Dim rngID As Range, c As Range
Dim lRow As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rngID = .Range("A2:A" & lRow)
End With

With Sheets("Bill Example ")

For Each c In rngID
Range("B10") = c

.PageSetup.PrintArea = "$B$2:$H$52"
.PrintOut
Next c
End With
Application.ScreenUpdating = True
Set c = nothing, rngID = nothing

End Sub
[/VBA]

You will need to modify the sheet names as appropriate for your live file..

perfect.. i just needed to amended the range to D10 and not B10 and it worked as I had hoped... thank you for your help....

[bacon]

Bacon, are you able to post a scaled-down version of the database? Strip out anything non-relevant to the invoices and just put a few dummy records in it (nothing confidential) and we may be able to help with the report.

ok great idea... shall I start a new thread and mark this one as solved??? what do you think?

[XLGibbs]

Hmmm....sounds like a potential candidate for my GetRowColumn method....