Macro to Generate new Quote TaB

[robertguy]

Hi,

What I require is the macro code to achieve the following:-

Cells A2, B2 and C2 will be completed manually, however, when a new quotation is required the user must enter a new Control Reference Number in cell E2 and then press the Generate button.

Once pressed the Macro behind the button will generate the following :-

(a) Quote number underneath the last one in column 'A'

(b) Change Ref underneath the last one in column 'B' (this was the ref number input above in E2)

(c) today’s date underneath the last one in column 'C'

(d) a blank Quote Tab i.e. labelled the next number in the sequence i.e. Quote 2, Quote 3 etc which should be created copying the Template Tab

N.B. the total number of Quotes in any one work book should be a maximum of 30 so some form of checking would be required.


Please see attached spread sheet as an example


Many thanks in advance for any assistance

Excel version 2003


Regards


Rob

[JBeaucaire]

This is the code I added into that Command Button:

Please Login or Register  to view this content.

It will provide an error if you click it without entering a value first.

[shg]

Try this:

Please Login or Register  to view this content.

[robertguy]

JBeaucaire & Shg

Many thanks for your prompt replies they both work a treat, BUT….

Is it possible to limit the number new quote tabs to 30


Many thanks


Rob

[JBeaucaire]

Here's the updated code, forgot to make it copy the template sheet. It also limits the workbook to 32 sheets, 30 Quotes plus your control sheet and template.

Please Login or Register  to view this content.

If you max the sheet you will receive an error prompt.
If you fail to enter a Control Code, you will receive an error prompt.

[robertguy]

JBeaucaire

Many thanks for the amendment it is very much appreciated, however, when I hide the ‘Template’ tab for cosmetic purposes any new ‘Quote’ tabs generated are also hidden.

Is there a way to hide the ‘Template’ tab and show all the ‘newly created Quote’ tabs when you press the Generate button ?


Additionally, sometimes when I delete any of the ‘Quote’ tabs generated and then generate a new sheet via the Generate button the newly formed Tab are named ‘Template 01, ‘Template 02’ etc. Is there a way to tweak the code so all Tabs generated will only be named ‘Quote xx’ as originally intended.

Any further assistance would be greatly appreciated


Regards


Rob

[JBeaucaire]

Add this line of code at the bottom of the macro:

Please Login or Register  to view this content.

I can't duplicate your second problem.

Would you like to clean up the input process? We could eliminate cell E2 and just put the prompt for the control reference # into the macro itself? Just an idea.

[robertguy]

JBeaucaire

Many thanks for the additional code it is very much appreciated.

As regards to cleaning up the process to eliminate cell E2 and just put the prompt for the control reference # into the macro itself that would be really good.

Is there anyway the prompt box could have two buttons i.e. ‘OK’ and ‘Cancel’ .

Any further assistance would be greatly appreciated.

Regards


Rob

[JBeaucaire]

Use this instead:

Please Login or Register  to view this content.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

[robertguy]

JBeaucaire

many thanks for all your advice it is very much appreciated and works better than I expected

Best Regards


Rob