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
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
> </o
>
Private Sub workbook_beforeprint(Cancel As Boolean)
<o
> </o
>
<o
> </o
>
'''''Printer Conformation.
<o
> </o
>
yesno = MsgBox("Proceed Print, Update Records?" & Chr(13) & "Choose no for an option to regular print." & Chr(13) & "Choose Wisely", vbYesNo + vbExclamation, "Important")
<o
> </o
>
Select Case yesno
<o
> </o
>
Case vbYes
<o
> </o
>
'''''Set the Workbook here. This is the one and only modification needed.(Once filepath is established)
<o
> </o
>
Dim myworkbook As Workbook
<o
> </o
>
Set myworkbook = Workbooks("Invoice.xls") '<-----'''Here is where to put your invoice name.
<o
> </o
>
Application.EnableEvents = False
<o
> </o
>
<o
> </o
>
Cancel = True
<o
> </o
>
<o
> </o
>
'''''Prints the active sheet
<o
> </o
>
ActiveSheet.PrintOut
<o
> </o
>
<o
> </o
>
Application.EnableEvents = True
<o
> </o
>
<o
> </o
>
<o
> </o
>
'''''Check if workbook is open.
<o
> </o
>
If Not WorkbookOpen("Record Log.xls") Then
<o
> </o
>
' Workbooks.Open ("C:\Users\standard account\Documents\Record Log.xls ")"<----Delete this line
<o
> </o
>
Workbooks.Open ("C:\Documents and Settings\(YOUR USER NAME)\Desktop\invoice_folder\Record log.xls")
<o
> </o
>
End If
<o
> </o
>
<o
> </o
>
myworkbook.Activate
<o
> </o
>
<o
> </o
>
'''''Copy invoice to logbook and then rename worksheet tab to invoice number
<o
> </o
>
<o
> </o
>
Range("l22").Value = Workbooks("Record Log.xls").Sheets("Summary").Range("a1").Value
<o
> </o
>
<o
> </o
>
'''''Increase invoice number by random number between 10, 20
<o
> </o
>
<o
> </o
>
With Range("L22")
<o
> </o
>
.Value = "R" & Right(.Value, Len(.Value) - 1) + Int((10 * Rnd) + 10)
<o
> </o
>
End With
<o
> </o
>
<o
> </o
>
Dim myname As String
<o
> </o
>
myname = Range("L22").Value
<o
> </o
>
<o
> </o
>
ActiveSheet.Name = Range("l22").Value
<o
> </o
>
Worksheets(myname).Copy after:=Workbooks("Record Log.xls"). _
Worksheets(Workbooks("Record Log.xls").Sheets.Count)
<o
> </o
>
Range("a1:m78").Select
<o
> </o
>
Selection.Copy
<o
> </o
>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
<o
> </o
>
Workbooks("Record Log.xls").Sheets("Summary").Range("a1").Value = Range("l22").Value
<o
> </o
>
Range("a1").Select
<o
> </o
>
<o
> </o
>
Application.CutCopyMode = False
<o
> </o
>
myworkbook.Activate
<o
> </o
>
ActiveSheet.Name = "Invoice"
<o
> </o
>
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
<o
> </o
>
<o
> </o
>
Case vbNo
<o
> </o
>
Cancel = True
<o
> </o
>
yesnno = MsgBox("<st1
lace w:st="on">Normal</st1
lace> print with no copy,invoice number increase etc.?", vbYesNo + vbQuestion, "Regular Print?")
<o
> </o
>
Select Case yesnno
<o
> </o
>
Case vbYes
<o
> </o
>
Application.EnableEvents = False
<o
> </o
>
Cancel = True
<o
> </o
>
ActiveSheet.PrintOut
<o
> </o
>
Application.EnableEvents = True
<o
> </o
>
Case vbNo
<o
> </o
>
End Select
<o
> </o
>
End Select
<o
> </o
>
<o
> </o
>
'''''Un-comment the below to auto save each open workbook, preserve new inv. num. and records.
<o
> </o
>
For Each w In Application.Workbooks
<o
> </o
>
w.Save
<o
> </o
>
Next w
<o
> </o
>
<o
> </o
>
End Sub
<o
> </o
>
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
<o
> </o
>
<o
> </o
>
<o
> </o
>
<o
> </o
>
<o
> </o
>
<o
> </o
>
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.
