Sequential Invoice...with a twist.

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
Dear R-amazing-man,

Ok, here's my results and feedback, thank you.

For your question, yeah, we tend to prefer having the 25 invoices as separate xls files...maybe because we're beginners, but also because it seems to me that, if what you're saying is that we could create one "sheet" for every invoice (product type), then the prob for us is that we like long/descriptive filenames, and you know excel doesn't allow many chars into those sheet names. Also it would take a while to find the right invoice for the customer and a lot of scrolling right or left. But that's why we currently keep our 25 invoices not in a single file. There may be a better way; we're still learning.

Your help is so awesome so far, my gosh, thanks.

For naming convention, I was thinking of the following to simplify things:

Kindly keep in mind that I didn't do any worksheet renaming cuz I don't quite understand what's being renamed, and you're probably understanding our setup better now (that the 25 excel files are within 1 folder). We could theoretically RENAME those long, descriptive excel filenames to 1, 2, 3, 4....25 if that helps, and call the folder itself "Invoices". This might make things much simpler.

IF your archiving solution can allow this idea, where the renumbering works and the archiving/renaming works with separate .xls files in one folder, that would be ideal. If not, then maybe you can suggest an alternate way we can keep our invoices in a "worksheet" but that allows us those longer more descriptive names we use, then, if we can do it, let's call those 2 worksheets "live_worksheet" and "archived_worksheet".

OK, here's what I get so far when I found & dropped the code into ThisWorksheet (excellent instructions by the way and thanks). (Again I didn't rename the worksheets since I'm kindof confused about how that will go down, and I'll await your response):

After the 3rd popup "Increasing invoice number....", I get "RUNTIME ERROR 438. Object doesn't support this property or method" (End/Debug).

If I choose "DEBUG", it opens the code and highlights the line:

Range("l22").Value = "R" & Range("l22").Value + Application.WorksheetFunction.RandBetween(10, 20)

I'm not sure if it's the "R" part that's the prob or if maybe they want an "L" instead of a lower case "l" for "L22".

As always looking forward to your words and code of wisdom.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,979
Office Version
2019
Platform
Windows
If I choose "DEBUG", it opens the code and highlights the line:

Range("l22").Value = "R" & Range("l22").Value + Application.WorksheetFunction.RandBetween(10, 20)

I'm not sure if it's the "R" part that's the prob or if maybe they want an "L" instead of a lower case "l" for "L22".
l22 is the same as L22, the range function is not case sensetive.

the problem is casued by the "R", I haven't read this entire thread in detail, assuming that L22 starts blank, first run will insert "R" with a random number (10 for simple example) into the cell.

Next run, L22 now holds a text string "R10", if random this time produces 5, you are expecting "R15",

what you are actually (trying) with that line of code is "R" & ("R10" +5), which, if it worked would give you "RR15", but the existing "R10" is a text value not numeric, you can't carry out a math function (in this case addition) on a text entry, so this is the cause of the error.

The cure is to extract the numeric part before increasing the value.

Code:
With Range("L22")
.Value = "R" & Right(.Value, Len(.Value) - 1) + Int((10 * Rnd) + 10)
End With
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Hello,

Jasonb75, Thank You for that, I think it will work.
I am still learning and appreciate your input.

thankyou

Your Very welcome, now lets see if we can really get this to work now.
Are you using Excel 2003?
Try these steps:

Create a Record Log file. Call it Record Log.xls
Replace thisworkbook module with this.

Code:
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("Record Log.xls") Then
    Workbooks.Open ("C:\[COLOR=red]Here change to your full file path[/COLOR]\Record Log.xls")
End If
 
'''''Copy invoice to logbook and then rename worksheet tab to invoice number
Sheets("Invoice").Copy after:=Workbooks("Record Log.xls"). _
        Sheets(Workbooks("Record Log.xls").Sheets.Count)
        ActiveSheet.Name = Range("l22").Value
 
  MsgBox ("Increasing Invoice number by a random number between 10 & 20")
 
 'Windows("Example of Invoice #1 of 25--all 25 will be in 1 folder.xls").Activate
'''''Increase invoice number by random number between 10, 20
With Range("L22")
.Value = "R" & Right(.Value, Len(.Value) - 1) + Int((10 * Rnd) + 10)
End With
'''''Un-comment the below to auto save each open workbook and preserve new inv. num. and records.
'For Each w In Application.Workbooks
'    w.Save
'Next w
Application.EnableEvents = True
End Sub
press alt + f11
open thisworkbook
delete previous
paste new code from above
edit the red above with your full file path
press alt + I ...let go press M
The modules will be below thisworkbook on the "TREE"
paste this into the module
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
This part above checks to see if a file is open. If not, opens it.
Close the Visual Basic Editor
Try a print and post back your results!


Congrats on the new machine...will check back tonight.
 

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
OK. We're back online. After countless hours (of copying and pasting) ;) computer probs, computer changes, reformatting & the usual drama, computer name changes, folder changes, folder name changes, and more, here's the code that works for Rman and me. It increases the invoice number after printing and archives the invoice under Record log. We're still working on getting the invoices in the folder to "remember" the last # used so that the next invoice that opens starts with that # and not the last # used by that actual spreadsheet.

This part goes under "ThisWorksheet' of each invoice in the folder:


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''

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

Application.EnableEvents = True


'''''Check if workbook is open
If Not WorkbookOpen("Record Log.xls") Then
'Workbooks.Open ("C:\Users\standard account\Documents\Record log.xls")
Workbooks.Open ("C:\PUT YOUR PATH HERE\Record log.xls")
End If

Windows("YOUR INVOICE NAME HERE.xls").Activate

'''''Copy invoice to logbook and then rename worksheet tab to invoice number

Dim myname As String

myname = Range("PUT THE CELL WITH YOUR INVOICE NO HERE").Value

ActiveSheet.Name = Range("PUT THE CELL WITH YOUR INVOICE NO HERE").Value

Worksheets(myname).Copy after:=Workbooks("Record Log.xls"). _
Worksheets(Workbooks("Record Log.xls").Sheets.Count)

'Windows("Record Log.XLS").Activate
Range("a1:m78").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("a1").Select
Application.CutCopyMode = False



Windows("PUT YOUR INVOICE NAME HERE.xls").Activate
ActiveSheet.Name = "Invoice"
MsgBox ("Increasing Invoice number by a random number between 10 & 20")

'''''Increase invoice number by random number between 10, 20
With Range("PUT THE CELL WITH YOUR INVOICE NO HERE")
.Value = "R" & Right(.Value, Len(.Value) - 1) + Int((10 * Rnd) + 10)
End With

'''''Un-comment the below to auto save each open workbook, preserve new inv.num. and records.

'For Each w In Application.Workbooks
' w.Save
' Next w


End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''


THEN create a module within your invoices with the following:

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


Hope this helps.
 

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
Repairman, thanks for the following contribution. In addition to prev features, it ensures that the last invoice no used is also where the next one begins so that invoice numbers can't go backwards depending on which invoice you just used. You just gotta put your preferred starting invoice no in a1 of record log. Quite nice.

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CCo-1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:eek:ffice:smarttags" name="place"></o:smarttagtype><!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> <o:PixelsPerInch>120</o:PixelsPerInch> <o:TargetScreenSize>1280x1024</o:TargetScreenSize> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--><!--[if gte mso 9]><xml> <o:shapedefaults v:ext="edit" spidmax="1026"/> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext="edit"> <o:idmap v:ext="edit" data="1"/> </o:shapelayout></xml><![endif]--> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
<o:p> </o:p>
Private Sub workbook_beforeprint(Cancel As Boolean)
<o:p> </o:p>

<o:p> </o:p>
'''''Printer Conformation.
<o:p> </o:p>
yesno = MsgBox("Proceed Print, Update Records?" & Chr(13) & "Choose no for an option to regular print." & Chr(13) & "Choose Wisely", vbYesNo + vbExclamation, "Important")
<o:p> </o:p>
Select Case yesno
<o:p> </o:p>
Case vbYes
<o:p> </o:p>

'''''Set the Workbook here. This is the one and only modification needed.(Once filepath is established)
<o:p> </o:p>
Dim myworkbook As Workbook
<o:p> </o:p>
Set myworkbook = Workbooks("Invoice.xls") '<-----'''Here is where to put your invoice name.
<o:p> </o:p>
Application.EnableEvents = False
<o:p> </o:p>

<o:p> </o:p>
Cancel = True
<o:p> </o:p>

<o:p> </o:p>
'''''Prints the active sheet
<o:p> </o:p>
ActiveSheet.PrintOut
<o:p> </o:p>

<o:p> </o:p>
Application.EnableEvents = True
<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>
'''''Check if workbook is open.
<o:p> </o:p>
If Not WorkbookOpen("Record Log.xls") Then
<o:p> </o:p>
' Workbooks.Open ("C:\Users\standard account\Documents\Record Log.xls ")"<----Delete this line
<o:p> </o:p>
Workbooks.Open ("C:\Documents and Settings\(YOUR USER NAME)\Desktop\invoice_folder\Record log.xls")
<o:p> </o:p>
End If
<o:p> </o:p>

<o:p> </o:p>
myworkbook.Activate
<o:p> </o:p>

<o:p> </o:p>
'''''Copy invoice to logbook and then rename worksheet tab to invoice number
<o:p> </o:p>

<o:p> </o:p>
Range("l22").Value = Workbooks("Record Log.xls").Sheets("Summary").Range("a1").Value
<o:p> </o:p>

<o:p> </o:p>
'''''Increase invoice number by random number between 10, 20
<o:p> </o:p>

<o:p> </o:p>
With Range("L22")
<o:p> </o:p>
.Value = "R" & Right(.Value, Len(.Value) - 1) + Int((10 * Rnd) + 10)
<o:p> </o:p>
End With
<o:p> </o:p>

<o:p> </o:p>
Dim myname As String
<o:p> </o:p>
myname = Range("L22").Value
<o:p> </o:p>

<o:p> </o:p>
ActiveSheet.Name = Range("l22").Value
<o:p> </o:p>
Worksheets(myname).Copy after:=Workbooks("Record Log.xls"). _
Worksheets(Workbooks("Record Log.xls").Sheets.Count)
<o:p> </o:p>
Range("a1:m78").Select
<o:p> </o:p>
Selection.Copy
<o:p> </o:p>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

<o:p> </o:p>
Workbooks("Record Log.xls").Sheets("Summary").Range("a1").Value = Range("l22").Value
<o:p> </o:p>
Range("a1").Select
<o:p> </o:p>
<o:p> </o:p>
Application.CutCopyMode = False

<o:p> </o:p>
myworkbook.Activate
<o:p> </o:p>
ActiveSheet.Name = "Invoice"
<o:p> </o:p>
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
<o:p> </o:p>

<o:p> </o:p>
Case vbNo
<o:p> </o:p>
Cancel = True
<o:p> </o:p>
yesnno = MsgBox("<st1:place w:st="on">Normal</st1:place> print with no copy,invoice number increase etc.?", vbYesNo + vbQuestion, "Regular Print?")
<o:p> </o:p>
Select Case yesnno
<o:p> </o:p>
Case vbYes
<o:p> </o:p>
Application.EnableEvents = False
<o:p> </o:p>
Cancel = True
<o:p> </o:p>
ActiveSheet.PrintOut
<o:p> </o:p>
Application.EnableEvents = True
<o:p> </o:p>
Case vbNo
<o:p> </o:p>
End Select
<o:p> </o:p>
End Select
<o:p> </o:p>
<o:p> </o:p>
'''''Un-comment the below to auto save each open workbook, preserve new inv. num. and records.
<o:p> </o:p>
For Each w In Application.Workbooks
<o:p> </o:p>
w.Save
<o:p> </o:p>
Next w
<o:p> </o:p>

<o:p> </o:p>
End Sub
<o:p> </o:p>
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
 

Forum statistics

Threads
1,089,337
Messages
5,407,676
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