Create Multiple Sheets from master sheet

Hi I'm sure this can be done but I cannot for the life of me work it out.

In a nutshell I want to copy (create) multiple workbooks and name them based on the list of filenames in Sheet1.

I am using Jerry Beaucaire's excellent FillOutTemplate http://www.excelforum.com/excel-prog...dashboard.html macro.

The code is working i.e. it is creating the workbooks, all named well and information inputted but I cannot get the code to include extra sheets (it will only copy the one sheet). How can I manipulate the code so that I can also add multiple sheets that appear in the original 'master' workbook? n.b I do not need this to be touched by the code that inserts the data.

Please Login or Register  to view this content.

Can this be done?

Thanks in advance for your help. :-)

Neil

Perhaps add this dim at the top:

Please Login or Register  to view this content.

Then this before you save the workbook:

Please Login or Register  to view this content.

Notice the array? Just edit those strings, add more, delete them, until you have a list of the sheets you want to add.


End result like:

Please Login or Register  to view this content.

Runtime Error 9 usually means an object by name cannot be found. Check your sheet names...any hidden spaces in there? Make sure the sheets are named exactly right.

If not, post up your sample files with the macro in it already and I'll take a look.

I seem to have gotten around that error but now I have a 1004 error, the attachment is on this thread

http://www.excelforum.com/excel-prog...-workbook.html

Thanks Jerry

Neil

Following on from http://www.excelforum.com/excel-prog...ml#post2521369

I am experiencing a 1004 error and not sure why.

Any ideas?

Hi,

Because you are trying to name the second new sheet you add with the same name (Input) as the first sheet you added. The names need to be unique and hence column C on the Data sheet which you are using to generate the names should contain unique values.

Regards

Feeling of deja vu?

With this version of your code, when you create your new workbooks, you copy additional worksheets. The last one to be copied is Renewal. This is the active sheet in the active workbook. So, when you say:

Please Login or Register  to view this content.

Range("B3") refers to the Renewal sheet and cell B3 has nothing in it.

Therefore, the save as fails (1004) because the filename is blank.


Regards

Not run the code? I'm hurt. I ran the code you provided in the workbook you uploaded. You wasted your time and mine in a fruitless endeavour. The original code fails when you create the sheets in the source workbook. I suspect the new code will do the same. The original code worked fine if separate workbooks are created. The new code fails, as I have explained, because you are copying additional sheets ... and the last one you copy remains the active sheet.

Your best bet is to define a variable and save the file name / scheme number while the Input sheet is active and then use the variable rather than cell B3 when you try to save the workbook.

Please Login or Register  to view this content.

Still think I haven't run the code? Had to be said! ;-)

Regards

Hi neilshaw

Wow

(sorry but it had to be said)

I should think TMShucks was offering his/her input. Wow.

@jaslake: ... *his* input. And it wasn't from a casual observation of the code, it was from testing it and debugging it ... which I'm guessing the OP isn't actually doing.

Regards

@TMShucks
I follow your (in this case HIS) work...I know what you do. The comments of the OP were offensive to me.

neilshaw

Don't start new threads, continue with the original until the problem is solved.

The Forum "limitations" do not stop you from adding a reply with an explanation that the previous attachment was wrong. I too would take ogffense if someone accused me of not running the code, and anyway it's often not necessary to run the code to see errors

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

@jaslake; @RoyUK: thanks for your comments.

@neilshaw: you should bear in mind that the people answering the questions do so voluntarily and you will not endear yourself to them by making accusations because you don't understand or like the response given. If it needs any clarification, I *was" offended by the suggestion that I had not run the code but more so by the comment "it had to be said" ... it truly didn't.

It's probably worth noting that neither of the code solutions you have posted will *ever* work if the option to create the new worksheets in the existing workbook is chosen ... you may as well not ask the question.

Regards

Originally Posted by TMShucks

It's probably worth noting that neither of the code solutions you have posted will *ever* work if the option to create the new worksheets in the existing workbook is chosen ... you may as well not ask the question.

Which is why I added the "extra sheets" code into the MakeBooks = True section. If you aren't making separate books, then there's no need at all to duplicate the other sheets, they are already in the workbook and you're just adding more filled out templates.

To both Neil and TMShucks, although the "it had to be said" comment adds an apparent disrespectful tone to the post(s), it is also possible to read the same posts with the comment as "not disrespectful". It truly is.

In truth, I have done exactly what Neil wondered out loud to you...I've many times posted on a complex macro purely from a visual analysis... and been right in my assertions. I've also been wrong. In neither instance was I being disrespectful by not running the code.

Go team!

==========

Neil, if that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.