Sequential Invoice...with a twist.

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
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, :eek:), 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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
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.
Code:
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.
Code:
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.
Code:
Activesheet.Name = Range("N1")
FYI I am not that good with vba.
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
I am not positive, yet this line may get you an increasing random invoice number:

Code:
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

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Here is a wat to get the "R" in front of the numbers.

Code:
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

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Try this:

test on a copy

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

Code:
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:

Code:
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

Board Regular
Joined
Aug 13, 2008
Messages
189
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

Board Regular
Joined
Aug 13, 2008
Messages
189
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

Board Regular
Joined
Aug 13, 2008
Messages
189
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

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
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

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Update:
Code:
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.
 

Forum statistics

Threads
1,089,337
Messages
5,407,666
Members
403,158
Latest member
Limerick2030

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top