VB Code to print a file

[AntEvans82]

Hi,

I am trying (desperately and unsuccessfully) to write code in Excel VB to print a file. In cell I9 there is an invoice number (say 100600) and I want this number to increment by 1 every time the macro is run to print the invoice. Ideally i want to print 150 each time in batch, so based on the starting invoice number being 100600 the ending invoice should be 100750.

I am really confused after being referred to several different places.

I would be very grateful if smeone could provide me with the code to do this.

Thanks in advance.

Ant

[opus]

Why don't you use the Macro-Recorder to generate code for you.
I used the Recorder and added a for loop

Sub Makro1()
Dim i As Long
For i = 100600 To 100750
Cells(9, 9).Value = i
    ActiveWindow.ActiveSheet.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
Next i
End Sub

You might want to adjudst the PrintArea!

[AntEvans82]

Wow - thanks for that.

That really works - just for me to really push my luck, do you think there is any way that you could help me with programming code to open a window that asks how many invoices I want to print?

It needs to be intelligent enough to recognise the value in cell I1 (not 9 as I previously wrote) and increment the vaue in cell I1 each time for the requested number of times.

Thanks and sorry to be a pain.

Ant

[opus]

Why don't you try yourself?
Cells(9,9) was adressing "I9", guess what you need to change that for "I1" (you have 2 choices!).
As a StartUP add a UserForm and put a Textbox and a CommandButton onto the UserForm.
The textBox should take the input for the number of invoices you want to print and the CommandButon should run the printing code (like posted above but using the value of Textbox.Text).
We will guide you through in case you run into problems.

[AntEvans82]

Hi,

Thank you - that is a relief, my very (very, very) basic knowledge of VB limits my ability to try it myself, if you could provide me with pointers, I would be very grateful.

Ant

[AntEvans82]

Also, you may be able to help me with another issue too (now I know I am pushing my luck.)

I have attached a workbook for you to see.

I have a sales order template that automatically generates and saves the workbook as a named sales order - what I really want it to do is to save the worksheet to a separate workbook where each sales order will have a separate tab - the sales agent then simply sends the workbook as one file via email to Head Office to process the sales orders in Sage.

My issue seems to be getting the sales order worksheet (prior to saving) into a separate workbook after the last processed sales order.

I think I am not too far off - but again I have just reached a stumbling block because my knowledge of VB is not what I would like.

Best wishes,

Ant