Copying worksheets and giving them names from a growing list.

Hi all,

I hope there is someone out there who can help me.
I am able to create some simple Macros, but nothing complex as I need it now. So any help you guys could provide would really be appreciated. I work with Excel 2010.

What I have so far:
I have 2 Worksheets.
Worksheet no. 1 is named "List"
Worksheet no. 2 is named "Template"

Currently worksheet no. 1 is empty. It will be filled line by line.
A1 will be "0001", B1 will be "name1", C1 will be "name2"

What the makro is doing right now:
1.) Copy worksheet "Template" put it before "Template" and name it according to what is in A1 (=0001)
2.) Copy B1 into G1 of the new worksheet "0001"
3.) Copy C1 into G4 of the new worksheet "0001"

That's how far I got on my own and it works!

But now comes the challenge:
Once a new line is added in worksheet "List" I want to use the macro again to:
1.) Copy worksheet "Template" put it before "Template" and name it according to what is in A2 now (which then would be 0002)
2.) Copy B2 into G1 of the new worksheet "0002"
3.) Copy C2 into G4 of the new worksheet "0002"
4.) Once another line 3 is added in worksheet "list", do step 1-3 again, and so on (each time a new line is added in worksheet "list").

Ideally:
Ideally I would have a button in my worksheet "List" that I can press once I added a new line and then what I described above happens automatically.

I hope I explained everything well enough. In case I did not and there are open questions, please do not hesitate to contact me!

Thanks in advance for your help!!!

You need to edit your thread title before receiving any help.
The title should be descriptive of your thread/task - not a plea for help. After all, helping to provide solutions is why the forum exists, so it doesn't need to be explicitly stated.

Here is what I have so far... maybe that makes it easier to understand! Still hope someone can provide help!

By clicking the button a new sheet is created for the first line (please try so that you can see what I am talking about).

Unfortunately no further sheets (0002, 0003, ...) are created by clicking the button again once I added a 2nd, 3rd, ... line. This is the part I am not able to figure out and where I need your help!

Thanks in advance!

Hi Testus,

Sometimes there are more questions than free help available to work on the questions so your question seemed to get overlooked or passed over. It is much easier to deal with questions if a sample workbook is included. We also like "fun" questions that we know can give a good, quick and accurate answer. We also get demerits if we answer questions that don't have code tags or wrong titles, which may have been the original problem. These good thread titles and code tags make this forum easier to search and read for everyone.

You finally included a sample workbook yesterday, showing some work on your part which makes us more motivated to help. Looking at your workbook though, it is not too healthy. All the merged cells are nightmares for us. We wish Microsoft had never included them as VBA is much harder with them included. Also you supply an older 2003 .xls file example but claim you are working in 2010, so why not a .xlsx?

Your recorded macro does work for the first new sheet but needs a little work. Find the attached where I've "fixed" a few things. See if this gets you going on what your final outcome needs to be.

Hi Testus
In response to your PM, try the attached. Let me know of issues.

Marvin, John,

you two guys are incredible! Thank you so much for the time you put into this.

I will now spend the next few hours studying your code and hopefully learning a lot from it. In case I have questions, I will send you a PM if you don't mind.

Thousand thanks again!!!

Marvin, in order to answer your question about me posting a 2003 file: not everybody that finally will share this file with me is working as I am with Excel 2010, so when I saved the file I had to convert it down to Excel 2003, so that in the end everyone who needs to have access to it can use it. And sorry for all the merged cells... I was not aware of that and will try to avoid them in future.

Hi Testus

I don't mind PM's. However, if you have a question relating directly to your Thread, it will be better for YOU if you post the question on the Forum. Why? Because you'll have the entire Forum Universe trying to help you. Another Why? Other OP's may have the same issue. They can learn from your issue.
I'll be glad to help however I can. You choose the line of communication...bear in mind, I may well send you back to the Forum if I feel it's in your best interest or the best interest of the Forum.

By the way...glad it works for you. If that satisfies your need, please mark your thread as "Solved".

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save

Hi Testus

I'll let MarvinP address this should he choose to do so. As he said, his suggestions were to "get you going". You'll need to modify MarvinP's code to test for the existence of the file. If you try to create the file and it already exists (and you don't test for it's existence), you'll get the error you described.

Compare the two codes. You can use elements of both to make either work for you.

After you've tried, if you're having issues, I'll be glad to help you through them. By the way, believe it or not, the code I presented IS your code except for testing for existence and except for the User Interface. Study it, you'll see. If you have problems understanding it, let me know.

Deleted...

Hi John,

I recognized that your code is mine basically (and also Marvin kept big portions of my original code). I am right now looking at the testing of the existance and try to understand how it works exactly. This will take some time for me...:-)

I might come back with some questions...

Hi Testus

Take your time...questions are welcome...don't guarantee answers. I should point out, there are other ways to do this. MarvinP loops through Column A to create the files...the code I provided allows the user to select the item in Column A for which the user wishes to create a file.

Another way would be to use Change Event code (Google it). I didn't think it appropriate here...but...look at it...may be what you're envisioning.

Welcome to the Journey...have fun with it.