Generate new worksheets from cell names and populate them with relevant info.

Please see original post which I have updated.

Based on your first uploaded work book I've written a macro that creates worksheets when there are values in the appropriate column i.e. will generate a worksheet named "Mens Doubles" but not a sheet named "Womens Doubles" as there are no values in column E aside from heading.

Re sheet names be aware that ":" is not a permitted sheet name so I've striped away the "Event: " part. A sheet name has also a maximum length of 31 letters and signs.

To test run macro "AddSheet". If this solution could be of use to you specify the changes you wish to have and I'll see if this could be implemented.

Alf

Hi Alf,

Thank you very much for your help.

Your macro is exactly what I wanted prior to the changes that I forgot to mention in post 1 (before I edited it!).

Would it be possible to adapt your macro to fulfill the changes? e.g. so that it could work for my file "sample_more" and copy the relevant template into each of the new worksheets?

Once again, thanks for all your help.

Skyping

Would it be possible to adapt your macro to fulfill the changes?

Hopefully yes but first I need to check with you that I do understand your problem properly.

The first sheet is "Event_final" then you have 3 template sheets and one "Choices" sheet.

The sheets you wish to add will all be put at the end i.e. after the "Choices" sheet and their names taken from row 1 sheet "Event_final".

The values in each column should be copied from row 3 down as far as the value goes i.e. 6 or 5 or whatever and pasted into the new sheet at cell B2.

If row 2 sheet "Event_final" contains a value i.e. "Template_abc", "Template_chips" or "Template_fish" the active range for the appropriate template should be copied to the new sheet as well.

So looking at "Template_abc" what is the active range? Cells B6:J20 or cells B10:J20? And where should this range be copied to cell C6 or cell D10 on the "Mens Singles" sheet?

I need the same information for the other two template sheets.

Final question what should happen if you wish to do a rerun of the macro? One way would be to let the macro start by deleting all sheets except "Event_final", the 3 template sheets and the "Choices" sheet and the rebuild everything based on new or changed values in "Event_final" sheet.

Alf

Hi Alf,

Thanks for getting back to me.

You are completely right in all your assumptions, except of the range of the various templates to be copied.

I intend for all generated sheets to have various lengths of entries starting from B2 and going down that column....this indicates the entries per event, as you have rightly stated.

All the templates (full versions) will start at C1 and go to z68 and I would like them to be pasted at the same location on the generated sheets, I simply put the dummy ones into my sample file for ease.

Additionally it would be great if when rerunning the macro, all of the previously generated sheets can be removed except of course the original ones.

Once again thanks for all your help.

Skyping

Ok got all information I need so I'll have a go at solving this.

Alf

Thanks Alf.

Ok her comes the new code. I've tested it on your last uploaded file andas far as I can see it works the way you wish it should do.

Macro starts by deleting all sheets except for "Event_final", the 3 template sheets and the "Choices" sheet.

Checks the number of used columns in sheet "Event_final" and loops through these columns creating new sheets if row 3 is not empty. It also copies the used range in each used column starting at row 2 and going down. Values are pasted to range B1 in new sheet. If there is a value in B1 the appropriate template sheet and range are copied to the new sheet and then cell B1 content is deleted.

There is someting funny with your data columns E, F, G and all the others that looks empty is not so because checking with vba it seems there are some invicible signs in thes columns as vba tells that the first 6 rows are filled.

Please Login or Register  to view this content.

Alf

Hi Alf,

Thank you so much for your excellent code...its exactly want I was looking for!!!

I greatly appreciate the work you have put into this, and I am continuing learning from you experts.

SOLVED

Thanks for feedback and rep!

Alf

Glancing at my code I see I've forgotten to add the line

Please Login or Register  to view this content.

at the start of the macro, if you also missed it just add the line before the

Please Login or Register  to view this content.

line. Sorry about that.

Alf

Good spot Alf....Thanks.