New Cell to make new worksheet

[newbie_excel]

Hi guys,

I have a workbook with 2 worksheets - "Numbers" and "Template"

In "Numbers", the A column has all our invoice numbers, the B column has names, and the C column has the paid amount.

Is there any way for excel to create a new worksheet for every new row in Column A of Numbers? The worksheet name should be the invoice number, and should be in the same format and design of the "Template" worksheet. Then within the new worksheet, the values of the corresponding B and C cells can be inserted into the relevant cells.

Please help!!

Thanks!

 

[mikerickson]

Yes, putting a routine in the Worksheet_Change event could do this.
What code do your have that (given a new invoice number) will create the new sheet?

 

[newbie_excel]

Hi Mike,

Thanks for the reply. I have no code whatsoever! I'm a complete novice at excel!

 

[mikerickson]

What does the Macro Recorder give you when you do it by hand?

 

[newbie_excel]

Hi Mike,

I have no idea how to do this by hand. I think this will simplify what I'm after:

How can I specify the new worksheet to be named after a given cell?
How could I create a new worksheet every time a new row was created in the Invoices worksheet?
How could I make the new worksheet the same template as the Template worksheet?

 

[mikerickson]

By "do it by hand", I meant do it once by hand, recording a macro. The code that will create can be made into an automatic routine.

I assume that Template is already set-up with the formulas and formatting that you want.

Right click on the tab of the Template sheet and select Move or Copy ... from the pop-up menu. (Put the copy at the end of the workbook.)
Double-click on the new sheet tab and change the tab name to match the invoice number.
Go to the Numbers sheet and copy the data from columns A-C. Then paste it into the appropriate places in the new worksheet.

After you have done this a couple of times, for practice, do it again, but with the Macro Recorder going. When finished press the Stop button.

This macro will be the basis for the eventual automatic routine you seek.