Sequential Invoice...with a twist.

[thankyou]

Hi. Hope your day is great. Wondering if you could help me with the following: I think it may be easy for you (but very appreciated by me!):

I have maybe 25 invoices in one folder, depending on what item the customer orders, and the invoice # is always in the same cell. What I need is a macro that will accomplish the following (it's kindof like all the other "sequential invoice" threads but with a couple variations). My skill level is neo-natal (I can find the macro window). Anyway, thanks:

Any/every time we PRINT ANY of the 25 invoices, I want that invoice to add, NOT "1", but instead add a RANDOM number that is BETWEEN, say, 10 & 20 to the LAST-PRINTED invoice number in that same folder. //So, for example if customer bought product A and that invoice # was R209360, I want the next customer's invoice (for same product/invoice or any of the others in that folder) to be any RANDOM # between R209370 and R209380.//

Also would like the invoice number to be like the examples above (starting with "R" and followed by 6 digits).

Finally (extra credit only, ), it would be really cool if on-save, if the invoices archive an EXTRA copy to another folder naming that extra copy with the invoice number itself.


THANX very much for your time and thoughtfulness. I wish I could help you too.

 

[thankyou]

OK, HERE'S WHERE I'M AT, thanks! ...

1. The first thing I'm checking out is the invoice no. Um *on me* doesn't seem to be changing when I PRINT/save/reopen or any combination. Just same invoice # keeps showing. My cell # is L22, so I naturally changed the 4 instances of a1 to L22 (used the upper case "L" by the way); hope that's ok.

I did NOT change any of the other items you had in red (ie the workbook names or sheets). I know what a sheet is, but is a "workbook" the same as the actual excel file? sorry for such a lame question.

Thanks and eager to note your responses to see what I'm doing wrong. thanks 10x so far.

 

[repairman615]

Type this formula into any cell:

=CELL("filename")

press enter


copy and then right click that cell, chose paste special, then choose values.

copy and paste that into a post here.

 

[repairman615]

Yes,

I would say the workbook is the collection of sheets and the name of it is the filename like yourworkbook.xlsm.

 

[repairman615]

I just reread your previous post. Only when you run this macro will anything happen.

Here is what is intended:
Your invoice should print, then a copy should be placed into another workbook you have and rename that sheet (tab)to its inv. num
then the original sheet should change the inv. number

If this becomes accomplished then maybe adding more code to "clear or reset the invoice sheet" saving you from having to delete cells contents.


If you post your filepaths (results of ///=cell("filename")///)I will help plug them in. also the filepath to your log workbook.

 

[thankyou]

R-man. Yeah, for sure, when you said

"I just reread your previous post. Only when you run this macro will anything happen.

Here is what is intended:
Your invoice should print, then a copy should be placed into another workbook you have and rename that sheet (tab)to its inv. num
then the original sheet should change the inv. number

>> Yeah, you totally understand the tasks on this. Sadly, none of that happens for me my friend.

If this becomes accomplished then maybe adding more code to "clear or reset the invoice sheet" saving you from having to delete cells contents.


If you post your filepaths (results of ///=cell("filename")///)I will help plug them in. also the filepath to your log workbook. "


>>Here's my path for the folder containing all the invoices is here:

C:\Documents and Settings\my-user-name\My Documents\Jdoc\INVOICES 7-2-10

Would it be at all possible to share a typical invoice xls file with you, and you can see with your own eyes what my prob is? I'm really interested to know (and of course excited to see it in action).

I really appreciate you!

 

[repairman615]

Hello thankyou,

I need still for you tell me what your workbook name is like...

Invoices.xlsm

Here is a way to get that:
Please type this into a any cell in your sheet and then tell me what its results are.

=cell("filename"[CODE]
(you can delete it when done)
 
Also where are your copies going to?  If another workbook then also repeat the above.
 
 
 
If you would like, upload your file to a place like box.net and provide the link.
 
Note:  You will need to make sure that your workbook is saved as a macro enabled workbook.  If unsure goto save as do that.

 

[thankyou]

oh for me, it's pretty simple. my xls filename always has one sheet I use, so let's just call my invoice or filename "Invoice".

The idea remember is that if I have. say, 25 different invoices within 1 folder, I want the one I pick (reflecting whatever of the 25 products the customer ordered) to show that new invoice number ON PRINTING, then archive that under the new invoice name elsewhere. I think we understand each other.

I can call the Invoice anything, for now, and I can call the Folder within which all are found "Folder". Doesn't matter. Hope that helps.

However, when I past the code, and hit "PRINT", the macro should run automatically (I shouldn't have to go in and find the macro and hit "RUN" right?). I want it automatic. WHen I hit PRINT though, the invoice number and none of the other stuff happens.

What am I doing wrong?

 

[repairman615]

I sent you a p.m. You can find that under the "Quick Links" in the blue bar above. Email me your file and I will have a look.

The idea remember is that if I have. say, 25 different invoices within 1 folder

I may be misunderstanding. Why not have them all in one excel workbook?

I am not sure this can be done just by hitting "print", however I will look into that and if possible will make changes.

You can try this macro by pressing Alt+f8 and then select this macros name and click run.

However, when I past the code, and hit "PRINT", the macro should run automatically (I shouldn't have to go in and find the macro and hit "RUN" right?).

Wrong, This all can be done through a button or a keystroke shortcut like ctrl + shift + P...then all should happpen (Print, make a copy, change inv.. num. etc.) So you will not have to hit "print".

If you get an error, post back the highlighted line of code and any error msg.

 

[repairman615]

Here might be a way to use the before print event (which should happen when "PRINT" is hit). This goes into a differnt spot.

Alt + f11
at the left pane you find "this workbook"
dbl. click and then copy / paste.
note: this still needs the red feilds changed
note: still uses the workbookopen macro posted earlier, which should be in a regula module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False

Cancel = True
'Prints the active sheet
ActiveSheet.PrintOut

'Check if workbook is open
If Not WorkbookOpen("2010 Rec Inv record log1.xlsm") Then
Workbooks.Open ("C:\Users\standard account\Documents\2010 Rec Inv record log1.xlsm")
End If

'Copy invoice to logbook and then rename worksheet tab to invoice number
Sheets("Invoice").Copy after:=Workbooks("2010 Rec Inv record log1.xlsm"). _
Sheets(Workbooks("2010 Rec Inv record log1.xlsm").Sheets.Count)
ActiveSheet.Name = Range("l22").Value


Windows("bid.xlsm").Activate
'Increase invoice number by random number between 10, 20
Dim mystring As String
mystring = Range("l22")
Range("l22").Value = Right(mystring, Len(mystring) - 1)
Range("l22").Value = "R" & Range("l22").Value + Application.WorksheetFunction.RandBetween(10, 20)
'For Each w In Application.Workbooks
' w.Save
'Next w
Application.EnableEvents = True
End Sub

 

[repairman615]

Here is one that I did get to work by hitting "PRINT".

This needs to go into ThisWorkbook
You can find that like this:

Press alt + f11
look to the left hand side on the tree
Find VBAProject(Yourworkbook.xlsm)
below the sheets listed should be ThisWorkbook
Double click that and then paste this in:

Private Sub Workbook_beforePrint(Cancel As Boolean)
 
MsgBox ("This shows that the Print Invoice Program is Running")
Application.EnableEvents = False
Cancel = True
MsgBox ("Printing Invoice")
 
'''''Prints the active sheet
ActiveSheet.PrintOut
 
'''''Check if workbook is open
'If Not WorkbookOpen("2010 Rec Inv record log1.xlsm") Then
 '   Workbooks.Open ("C:\Users\standard account\Documents\2010 Rec Inv record log1.xlsm")
'End If
 
'''''Copy invoice to logbook and then rename worksheet tab to invoice number
'Sheets("Invoice").Copy after:=Workbooks("2010 Rec Inv record log1.xlsm"). _
 '       Sheets(Workbooks("2010 Rec Inv record log1.xlsm").Sheets.Count)
  '      ActiveSheet.Name = Range("l22").Value
 
  MsgBox ("Increasing Invoice number by a random number between 10 & 20")
 
 'Windows("bid.xlsm").Activate
'''''Increase invoice number by random number between 10, 20
Dim mystring As String
    mystring = Range("l22").Value
        Range("l22").Value = Right(mystring, Len(mystring) - 1)
        Range("l22").Value = "R" & Range("l22").Value + Application.WorksheetFunction.RandBetween(10, 20)
'For Each w In Application.Workbooks
'    w.Save
'Next w
Application.EnableEvents = True
End Sub

This should Print, increase invoice number.

Still not finished yet closer.