Auto making of new sheet

[stojko89]

I have a table on sheet 1.
For what I want to happen I don't know if I have to use a makro (don't know how to use makros) or can I use some other functions.

What I want to happen is:

When I write 0001-2011 in colum 1 (cell A2 - example) I want when I hit enter a new sheet is made in this workbook named 0001-2011 and in the colum (cell - A2) I wrote 0001-2011 I want it to be a hiperlink to this sheet.

Later on there will be like 250 Sheets named like 0001-2011, 0002-2011 and so on...
And I also have a table that I want to be automated on this new sheet.

Is this even possible?

Idias, please

Denis

[royUK]

It's only possible with a macro.

It seems poor design to have 250 identical sheets in the workbook

[stojko89]

Problem is, I have to be able to look in to history of what we bought and what we're still waiting to get and so on, that's why the hiperlinks are there.
The would look kind of the same yes but would make a very big differenc to me and a few guys that would also be working with this.

So can someone help me with the makro I'm sopose to make? I don't even know how to start it, I've never done any makros.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Marcol]

Try this demo workbook.

Enter your new sheet name in column A.

I presume you will change the data in your table before you generate a new sheet, this example will only handle ten rows of data as it stands.

If this is similar to what you need, then do as Roy says, and post a sample of your workbook and we can explain it from there.

[stojko89]

Yes Marcol, something like that
But I need to understand what I'm doing, couse later I may want to change some stuff in the macro (I'm not saying I will) but you can never know

I have attached the file I'm working on.

[Marcol]

Could you explain what table you need copied to the new sheet and what data it should contain?

[stojko89]

I need table 00001-2011 to be copyed on every new sheet when the hyperlink is made. But It has to look like this, couse latter the table on sheet 00001-2011 will be filled with some datta and I don't want that data to be copyed on the new sheet. I hope you understand what I mean

[stojko89]

The table can also be on the sheet 'Pomoč' and it can be empty and copied from that sheet to every new sheet, it wouldn't bother me if it's there.

[stojko89]

And also I have a problem with the drop down menus on sheet 00002-2011
I know why, but I don't know how to fix it? can it be made so

Please Login or Register  to view this content.

is dynamic? So it changes from 00001-2011 to witch sheet I'm working on, In this case it's 00002-2011 next time it will be 00003-2011...and so on.

I've attached a file, try using the drop down menus on sheet 00002-2011 and then go check under formulas - names. And you will see what my problem is.

Please help. My boss is pressuring me and I don't know what to do anymore

[stojko89]

Ok so the drop down menu is fixed. Thanks to DonkeyOte
But will anyone else help me with the makro? I'm trying to write the makro on my own but I don't have a clue what I'm doing.

[Marcol]

The original code I gave you does the job if you just change the range to be copied.
The code goes into the Sheet Module , not a Standard Module, and is the Sheet Change Event.

I have put the code, with a few refinements into this sample workbook.
To view this code, in the proper module, right click the tab of Sheet "Narocila" and select View Code.

I have added a Named range "TableTemplate" to make copying the table a little easier.

I hope this pleases your boss.

[stojko89]

Marcol, Thank you very much.
It didn't work good for me the first time becouse I didn't jet know about the

Please Login or Register  to view this content.

Now that I do it works perfect. Just the way I wanted!
I did make a little change to make it easyer but it doesn't matter.

I shall be forever grateful.
Thank you very much.

Regards, Denis