Question MS Excel question -invoice number

[Bj]

Does anyone know how to make something like an invoice number in Excel? I know how to place page number, but the problem is it will always print page 1 or 2... if the document is printed 100 times, I want each page to have a count, 1,2,3...ext.

I can write a Macro if needed, but does anyone else know of any other methods?

 

[insanity]

What kind of shape is your data in? Is it all collated in a workbook or is each spreadsheet saved separately? I could tell you a solid way for either.

 

[Bj]

What kind of shape is your data in? Is it all collated in a workbook or is each spreadsheet saved separately? I could tell you a solid way for either.

I believe it is in one workbook. There is 1 long page to be printed as a single page double sided, if that makes a difference...

 

[pattersoncr]

Yuo can change the number that the page numbering starts on:

Change the page number for the first page

1. <LI class=LPROC>Click the worksheet. <LI class=LPROC>On the File menu, click Page Setup, and then click the Page tab.
2. In the First page number box, type the page number you want to appear on the first page of the worksheet. To have Microsoft Excel automatically number the pages of the worksheet, type the word Auto in the First page number box.

You can also go to View => Page Break Preview
to change where the page breaks occur.

 

[insanity]

I thought he wanted unique pagination to appear on the invoices themselves too. The best way is to create a generic invoice form with a small macro that adds 1 to cell a1 (either a keystroke macro or a context event macro). Keep the generic form in a separate worksheet. Copy the actual data on-top of the form, running the macro before each new invoice is copy/paste. Then copy the correctly numbered invoices back into your original workbook. This is nice because you can then use these nice invoice sheets continually and there is a constant record.

That would be the 'clean' way to do it.

This is a dirty way:
Enter a number on cell a1 on the first invoice, probably a 1. Then drop this into every other worksheet's cell A1 =(Sheetnamewhichinvoice1ison!A1+1). Every time you copy/paste it into a worksheet, just add another 1.

 

[Bj]

Yuo can change the number that the page numbering starts on:
[/LIST]
You can also go to View => Page Break Preview
to change where the page breaks occur.

Not exactly what I had in mind, I know you can set the spreadsheet pages to show page 1,2,3... but if you print like 10 copies of the spreadsheet, all pages would say 1,2,3...

It would be like if I had one page on the spreadsheet and printed it 10 times, the last page will say 11.

I thought he wanted unique pagination to appear on the invoices themselves too. The best way is to create a generic invoice form with a small macro that adds 1 to cell a1 (either a keystroke macro or a context event macro). Keep the generic form in a separate worksheet. Copy the actual data on-top of the form, running the macro before each new invoice is copy/paste. Then copy the correctly numbered invoices back into your original workbook. This is nice because you can then use these nice invoice sheets continually and there is a constant record.

That would be the 'clean' way to do it.

This is a dirty way:
Enter a number on cell a1 on the first invoice, probably a 1. Then drop this into every other worksheet's cell A1 =(Sheetnamewhichinvoice1ison!A1+1). Every time you copy/paste it into a worksheet, just add another 1.

Actually the sheets are blank, the only thing that changes page to page is the invoice number. It really is just a fill out form for automotive guys. They are hoping to print a hundred copies of the invoice and it would be better for it to be done automatically rather than one at a time.


Just in case; does anyone know if this can be done easily with MS Access?

Edit: I think I could write the macro, just because I know a little bit about VB, but I cannot find a reference site for VBA liek MSDN library. Can anyone find one or know of one?

 

[insanity]

ahh. Try this, it's pretty simple but it will put a new number every time you open the workbook.

Private Sub Workbook_Open()
Range("a1").Value = Range("a1").Value + 1
End Sub

 

[Bj]

ahh. Try this, it's pretty simple but it will put a new number every time you open the workbook.

Private Sub Workbook_Open()
Range("a1").Value = Range("a1").Value + 1
End Sub

Ok that works to add 1 for ever time I open the sheet, but what about printing? It still is going to print 1 or 2 or 3 or whatever finite number I have placed on the page. The number should be a little more variable.

 

[insanity]

Open, print, repeat?

 

[Bj]

Open, print, repeat?

100-500 times?

If only I can find a good reference site for VBA then maybe I could come up with something.

 

[insanity]

I was thinking about this and forgot to post this, but you could have your page number macro and a simple pearl program that opens, prints, saves, and repeats x times.

http://www.cs.mcgill.ca/~abatko/com...wto/open/#open_a_file_and_print_its_contents_