OK
Assumes that Quote is name of master invoice sheet to be copied.
Paste this code to a general module in your workbook.
Quote sheet numbers are stored in the Registry.
Thanks to JE McGimpsey for most of the code.
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
Sub CreateNewQuoteSheet()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Quote"
Const sKEY As String = "Quote_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long
Dim QuoteWks As Worksheet
Dim client As String
Set QuoteWks = Worksheets("Quote")
client = InputBox("Enter Client Name")
QuoteWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
With ActiveSheet
.Name = client
'adding the date is optional. remove next 6 lines if not wanted
With .Range("D1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("F1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub
Gord
On Fri, 29 Jan 2010 11:17:21 -0800, Lee
wrote:
May plans are after a month or so we will delete the quotes (sheets) so I
think the first option will probably be the best.
"Gord Dibben" wrote:
You can do it in one workbook by copying and renaming the unfilled Master
sheet with an incremented invoice number.
But........If you will have many of these quotes/invoices you will
eventually get a workbook that is quite large and unmanageable.
You may be better off creating a Template workbook(*.xlt or *.xltm)
This will give you a new workbook for each client.
Then you would need VBA code to create the new invoice number when you open
the Template.
Which way do you want to go?
I can provide you a sample for each.
Gord Dibben MS Excel MVP
On Fri, 29 Jan 2010 08:36:11 -0800, Lee
wrote:
I have a quote form that will be used for business. At the top it has an
invoice number. When a new person comes in to get a quote i will open this
workbook and copy a new sheet from the master sheet, or the previous sheet.
Is there a way that a new invoice number can automatically come up just from
copying the previous sheet? Thanks for the help.
.