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.

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.

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

On Fri, 29 Jan 2010 10:24:48 -0800, Gord Dibben <gorddibbATshawDOTca 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.

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.

.

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.

.