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.

 

[repairman615]

I also keep track of invoices. Here are a few peices of code to get you started.

If you are familiar with the macro recorder then try it to acomplish your second part. Then post back the code to receive help with amendments.

Here this line adds 1 to the invoice number in cell L12.

Range("l12").Value = Range("l12").Value + 1

This line makes a copy of the sheet and places it as lastsheet in a different work book.

Sheets("Invoice").Copy after:=Workbooks("2010 Rec Inv record log1.xlsm"). _
        Sheets(Workbooks("2010 Rec Inv record log1.xlsm").Sheets.Count)

This line renames the worksheet to the value in N1. Note this could be you invoice number cell.

Activesheet.Name = Range("N1")

FYI I am not that good with vba.

 

[repairman615]

I am not positive, yet this line may get you an increasing random invoice number:

Sub randinvoice()
Range("[COLOR=red]a1[/COLOR]").Value = Range("[COLOR=red]a1[/COLOR]").Value + Application.WorksheetFunction.RandBetween(10, 20)
 
End Sub

Change the red to your cell that contains the invoice number.

Practice on a copy of your workbook.

I will keep trying to be progressive.

 

[repairman615]

Here is a wat to get the "R" in front of the numbers.

Sub randinvoice()

Dim mystring As String
mystring = Range("a1")
Range("a1").Value = Right(mystring, Len(mystring) - 1)
Range("a1").Value = "R" & Range("a1").Value + Application.WorksheetFunction.RandBetween(10, 20)

End Sub

I am thinking it would be possible to have a macro code to print the invoice, then log the invoice to Your Log workbook, and finally increase the invoice number.

 

[repairman615]

Try this:

test on a copy

Open the VBA editor (alt + f11)
Insert two modules
In the first copy and paste:

Sub randinvoice()
'Prints the active sheet
ActiveSheet.PrintOut
 
'Check if workbook is open
If Not WorkbookOpen("[COLOR=red]2010 Rec Inv record log1.xlsm[/COLOR]") Then
    Workbooks.Open ("[COLOR=red]C:\Users\standard account\Documents\2010 Rec Inv record log1.xlsm[/COLOR]")
End If
 
'Copy invoice to logbook
Sheets("[COLOR=red]Invoice[/COLOR]").Copy after:=Workbooks("[COLOR=red]2010 Rec Inv record log1.xlsm[/COLOR]"). _
        Sheets(Workbooks("[COLOR=red]2010 Rec Inv record log1.xlsm[/COLOR]").Sheets.Count)
 
 
'Increase invoice number by random number between 10, 20
Dim mystring As String
    mystring = Range("[COLOR=red]a1[/COLOR]")
        Range("[COLOR=red]a1[/COLOR]").Value = Right(mystring, Len(mystring) - 1)
        Range("[COLOR=red]a1[/COLOR]").Value = "R" & Range("[COLOR=red]a1[/COLOR]").Value + Application.WorksheetFunction.RandBetween(10, 20)
 
'Optional save all open workbooks
'For Each w In Application.Workbooks
'    w.Save
'Next w
 
End Sub

Change the red cells to suit.

In the second module, Copy / Paste:

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    Application.ScreenUpdating = False
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
Application.ScreenUpdating = True
End Function

Close the editor.

You could link this macro to a button (set to not print)
or set a shortcut key stroke like ctrl + shift + p (Press alt + f8 / options)

Any real Vba guru's please feel free to let me know any insight you may have!

 

[thankyou]

R-man, you're blowing me away with your help, thank you! I just wanted to be honest and let you know that I'm starting to work on all ur things, and will follow-up so you don't wonder "where the heck is that guy?" lol. THANKS! I'm eager to try your advice. Starting now...

 

[thankyou]

PS...also, hope i didn't mess you up when i used the word "macro". I guess I just meant the code stuff we're talking about when we go Alt F11 > Insert > Module. (not recorded macros). That's what i'm doing. Sorry if confused ya on that one thing. Seems very helpful...checking it out now...

 

[thankyou]

ah, i understand the difference, now, re meaning of macro as applies to excel. that will just be the cherry on top if i can get that far. will be awesome...ok, will check back shortly.

 

[repairman615]

It is a macro...I think..hah.

By the way, I am serious when I say I am not that good...I am very new to this. I never new about this side of excel and I have been using for more than 6 years.

 

[repairman615]

Update:

Sub randinvoice()
'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
Sheets("Invoice").Copy after:=Workbooks("2010 Rec Inv record log1.xlsm"). _
        Sheets(Workbooks("2010 Rec Inv record log1.xlsm").Sheets.Count)
        [COLOR=lime][COLOR=darkred]ActiveSheet.Name = Range("[COLOR=red]a1[/COLOR]").Value
        
    
 Windows("[COLOR=red]bid.xlsm[/COLOR]").Activate[/COLOR]
[/COLOR]'Increase invoice number by random number between 10, 20
Dim mystring As String
    mystring = Range("a1")
        Range("a1").Value = Right(mystring, Len(mystring) - 1)
        Range("a1").Value = "R" & Range("a1").Value + Application.WorksheetFunction.RandBetween(10, 20)
'For Each w In Application.Workbooks
'    w.Save
'Next w
End Sub

added code to rename sheet.