Invoice Number

[Lee]

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.

 

[Gord Dibben]

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

 

[Gord Dibben]

If you want, send me a copy of the workbook you currently have and a
description of which method you want to use for creating a new invoice.

I can build you something.

email me at gorddibbATshawDOTca change the obvious.


Gord

 

[Lee]

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]

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