Need sequential numbering for quote form

[Ital]

Hi All,

I'm a very basic excel user and setting up a quote form for a small business. The boss wants the quote numbers to generate automatically each time a new quote is started, is there a way to do this easily?

Thanks so much for any help.

M

 

[WhoCanDo]

Is the new quote a new workbook or a new worksheet or a new line?

Regards

 

[repairman615]

Hello,

Welcome to the board!


This is very possible.

How are the quotes being recorded?

Is there a list with the quote numbers in a summary perhaps? If so using =Max(your list)+1 may work.

Or is this a template that gets copied or saved manually or macro?

Also what cell houses the quote number?

Another option would be to add 1 to the cell everytime the workbook is opened.

Please give further detail and I am sure you can acheive this task.

 

[Ital]

The option of adding +1 each time the workbook is opened sounds like what I need. How do I do that?

I've just set up a quote form which I will open and enter the info into and then save it each time under it's quote number.

Sorry if I'm obtuse, but i'm very new to Excel!

 

[Ital]

Also, quote number is in cell H4.

Thanks!

 

[repairman615]

Hello,

Try this:
Copy the below

Sub SaveQuote()
    ActiveWorkbook.SaveCopyAs "C:\[COLOR=red]YOUR\FILE\PATH[/COLOR]\" & Sheets("Quote").Range("H4") & ".[COLOR=black]XLSM[/COLOR] "
 
    Sheets("[COLOR=red]Quote[/COLOR]").Range("H4").Value = Sheets("[COLOR=red]Quote[/COLOR]").Range("H4").Value + 1
    ActiveWorkbook.Save
 
End Sub

In your Quote workbook, press Alt+F11
Use the keystrokes Alt - I - M to insert a module
Paste the code
Now make changes to the code in the areas highlighted in red
(Enter your file path and your sheet name to the "template quote sheet")
close the Visual Basic Editor

Now insert a button onto the quote sheet and assign this macro (SaveQuote)

When you press the button, the quote should be saved as the quote number and the quote number should add 1 and save also.

This could be made better as it does not handle any errors, yet it seems a good place to start.