<pre>
Hi
There's no need to combine the workbooks into one.
If you want a customized example of the code below,
then please list the following.
The complete paths of all 8 of your workbooks and the
cell which holds the invoice number for each:
Ex C:My DocumentsTemplateOne.xls
Invoice number in cell D1
If you can edit the procedure yourself then there you have
it. This will add a button to the right-click "Cell"
commandbar popup called "Invoice Number"(The popup menu
which appears whenever you right click any given cell).
This button is added whenever any of these workbooks
are opened. All you will need to do to enter the next
logical invoice number is to right-click on the cell
containing the invoice number and then click the button
on the popup menu. The number may be placed in any cell
but will only be incremented if placed in the cell address
given in the routine.
This code will need to be pasted into the workbook module
of all of your invoice template workbooks. I have added
some descriptive variables which you can easily change the
assignments. To paste into the correct module, do the
following. From Excel's main window, right-click the
Excel icon located immediately left of the "File" menuitem,
choose "View Code", paste in as is.
'##################################################
CODE:
Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Cell").Controls("&Invoice Number...").Delete
Err.Number = 0
With Application.CommandBars("Cell").Controls.Add(msoControlButton, , , , True)
.Caption = "&Invoice Number..."
.OnAction = "ThisWorkbook.NextNum"
If Err.Number <> 0 Then _
Application.CommandBars("Cell").Controls("&Invoice Number...").Delete
.TooltipText = "Click to enter the next invoice number."
.Style = msoButtonCaption
End With
End Sub
Public Sub NextNum()
Dim TextFormula(1 To
As String
'these formulas are temporarily added to your workbook to link to the
'other 7 closed or open templates to discern the
'next logical invoice number and need to be typed in correctly
'Example:
'TextFormula(1) = "='C:Documents and SettingsAdministratorDesktopExcelIncoices[TemplateOne.xls]Sheet1'!$A$1"
'The above formula is simply linking the open workbook to Cell A1 in the workbook located at this path.
'Fill in the 8 below with all 8 of your templates paths and references
TextFormula(1) = ""
TextFormula(2) = ""
TextFormula(3) = ""
TextFormula(4) = ""
TextFormula(5) = ""
TextFormula(6) = ""
TextFormula(7) = ""
TextFormula(8) = ""
With Sheets(1)
.Cells(65001, 256) = TextFormula(1)
.Cells(65002, 256) = TextFormula(1)
.Cells(65003, 256) = TextFormula(1)
.Cells(65004, 256) = TextFormula(1)
.Cells(65005, 256) = TextFormula(1)
.Cells(65006, 256) = TextFormula(1)
.Cells(65007, 256) = TextFormula(1)
.Cells(65008, 256) = TextFormula(1)
ActiveWorkbook.UpdateRemoteReferences = True
ActiveCell.Value = WorksheetFunction.Max(Range(.Cells(65001, 256), .Cells(65008, 256))) + 1
Range(.Cells(65001, 256), .Cells(65003, 256)).Clear
End With
End Sub
'##################################################
</pre>
Tom
