PDA

View Full Version : Print all pages



bacon
02-23-2006, 12:57 AM
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
02-23-2006, 07:46 AM
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
02-23-2006, 07:59 AM
You are a superstar.. thank you :)

XLGibbs
02-23-2006, 08:15 AM
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
02-23-2006, 08:52 AM
Ok i am going to reload the spreadsheet as I uploaded it without saving which is why it is confusing...:banghead:


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
02-23-2006, 08:59 AM
the print area in Bill example is B2:H52...

Norie
02-23-2006, 09:17 AM
bacon

I notice there's a link to MS Access.

Could this all not be done there using a report?

bacon
02-23-2006, 09:20 AM
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
02-23-2006, 09:37 AM
This might be a stupid question, but why?:)

bacon
02-23-2006, 09:44 AM
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
02-23-2006, 10:09 AM
No need for changing the database, if that's what you mean.

Just create a report.:)

And Access has various wizards for that.

bacon
02-23-2006, 10:31 AM
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
02-23-2006, 01:56 PM
Try this out Bacon.

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


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


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

geekgirlau
02-23-2006, 11:49 PM
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
02-24-2006, 01:32 AM
Try this out Bacon.

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


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


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
02-24-2006, 01:33 AM
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
02-24-2006, 03:36 PM
Hmmm....sounds like a potential candidate for my GetRowColumn method....