LIST to TABS: Creating a copy of an existing tab for each name in a column

**Geoff.** · 08-20-2014, 05:39 PM

Hello,

I'd like to see if anybody can help me write some code that will:

a) Create a copy of 'Sheet 2' for each name in column 'A' from Sheet1 (for example, names would be in Sheet1!A2:A12 (typically 10 names but can be more [A2:An])

b) Name the newly created tabs after each of the names in the list (as mentioned above in "a")

c) Insert each unique name in cell A1 of the new tabs that were created

d) If a sheet already has one of the names in the list, the sheet with that name should not be replaced and this name should be skipped but the operation should continue for the other names

An example workbook is attached here which shows the intended results. Workbook: List to tabs.xlsx

Even if you only have advice on where to start, please share.

Thank you,

Geoff.

**Paul** · 08-20-2014, 07:12 PM

Something like this should work, Geoff...

Please Login or Register  to view this content.

**Geoff.** · 08-21-2014, 02:46 PM

Hello Paul,

Thank you very much for this code. It is helpful and certainly much better than what I was trying to do!
I have run in to a few issues though and I am hoping you can have a look at them and maybe help me resolve them.

- In the attached file, when I run the macro for column A:A in 'Sheet1', I get the expected results but when I run the macro for column B:B which contains a formula to only return unique values and also excludes values containing "IC", I get the undesired result of the macro including values containing "IC" (thus creating a new sheet with these values). The macro also tries to create a new sheet for cells which only contain the formula in Sheet1!B:B (these cells contain a formula but the formula is intentionally not returning any results- the macro still reads the formula and creates a new sheet for these "blank" cells and then tries to name it, which results in an error. Can the macro only use those names that are visible in column B:B?

- One seconde note, is it possible to set the macro so that if a sheet already exists with one of the names in the list that it just skip that name? Sometimes names will be added or removed from the list.

Here is file in case you are able/willing to have a look: List to tabs macro with unique list formula troubleshoot.xlsm

Thank you in advance,

Geoff.

**Geoff.** · 08-27-2014, 12:38 PM

Is there anyone else that thinks they can add to Paul's already great imput?

Thanks!

Geoff.

**Geoff.** · 08-27-2014, 12:51 PM

Bump no response

**jaslake** · 09-03-2014, 01:52 PM

Hi Geoff

Try this

Please Login or Register  to view this content.

**Geoff.** · 09-09-2014, 02:26 PM

Hi jaslake,

Thanks for your reply. I have tried running your code and unfortunately, I run into the same issue. It ends in a error (see attached screen shots).

I think the problem is that because the column in "Sheet1" (in the screenshots I used "Template1") that this macro is pulling from has 20 or so rows that contain a formula, but usually only about 12 or so rows will return text (names in this case) when the formula is calculated (it's variable;sometimes it's only 10 names, sometimes it can be 15 names).

Is there a way to have the macro not create a new sheet if the next cell in the column does not return a name (the cell it is not trully blank since it contains a formula but that formula is not returning a name).

Screenshots:
runtime_error1.png runtime_error2.png

Below is the formula being used in the column in question in case it helps:

Formula:

Please Login or Register  to view this content.

Thanks,

Geoff.

**jaslake** · 09-09-2014, 02:50 PM

Hi Geoff

The Code in your Screen Shots is NOT the Code I posted. Try the Code I posted.

**Geoff.** · 09-09-2014, 04:20 PM

Oh, Thanks for pointing that out... I accidently used the one that was posted by Paul. Sorry about that...

I'll try out your code right away!

Geoff.

**Geoff.** · 09-09-2014, 05:02 PM

Hi again John,

Your code worked marvelously on the sample workbook that I attached here, thanks for that. I just have trouble adapting it to my work book.

What does the !A1 in the following line refer to?

Please Login or Register  to view this content.

When I tried to adapt to another workbook, an error occured at that line.

I am trying to adapt your code for:
- Template1/Template2 instead of Sheet1/Sheet2
- To column Y in Template1 instead of column B in Sheet1
- To cell U1 in the new sheets formed instead of A1

So to do this I just modified the values in your code as follows but I get an error at the above mentioned line. Do you see what I did wrong?

Please Login or Register  to view this content.

Thanks again, Geoff.

**jaslake** · 09-09-2014, 05:59 PM

Hi Geoff

Firstly change the "U1" back to "A1"...for the moment.

Secondly, what does ".Range("Y" & i).Value" evaluate to...any spaces? Show me some examples...it's a bit tough debugging without a File.

Thirdly, what error message are you getting?

**Geoff.** · 09-09-2014, 07:20 PM

Hi John,

Ok. I've changed the U1 back to A1...

I understand, I'm not sure what to do. I am trying to get a sample file to give me the same error so that I can send it to you but it's not working at the momment (in order words your codes works fine on the sample file). Sorry.

Here are some screen shots of the error: runtime_error13.png, runtime_error13 (2).png.

How do I evaluate a segment of code?

Y would refer to a column contain a text header and about 20 or so rows of array formula (about half of which would return text).

Does this help at all?

What else can I do to help?

Geoff.

**jaslake** · 09-09-2014, 08:10 PM

Hi Geoff

What are the Values in Column Y? Are there any Spaces in the Values? Show me.

**Geoff.** · 09-10-2014, 11:45 AM

Ah yes, there is a space and a "," with each name... sorry I didn't realise that would have an impact on the macro.

I added a comma and space to the names in the example workbook and got the same error. See attached: List to tabs macro with unique list formula troubleshoot.xlsm.

Thanks for continuing to help me with this,

Geoff.

**jaslake** · 09-10-2014, 12:02 PM

Hi Geoff

Modify the Line of Code indicated. Notice the addition of the Single Quote Marks which accommodate the Spaces in the Names.

Please Login or Register  to view this content.

**Geoff.** · 09-10-2014, 12:44 PM

John, it works like a charm! Thank you very much

Impressed,

Geoff.

**jaslake** · 09-10-2014, 02:10 PM

Hi Geoff

You're welcome...glad I could help. Thanks for the Rep.

I didn't answer your question regarding this

Please Login or Register  to view this content.

The Code is looking to see if the sheet Name (.Range("B" & I).value) and Cell Reference ("A1") is a valid Reference. If not valid (False), the Code creates the new Sheet and Names it appropriately. If it is a valid (True) Reference the Code skips it.

So, in reality, any valid Cell Address can be used.

**Geoff.** · 09-10-2014, 06:07 PM

Hi John,

I understand now. I still don't understand other parts of that code but I will try to figure it all out. I could see that this part of the code was probably was lacking in the orignal code that I posted so I was curious to know if I was correct.

Thanks for remembering to address that question of mine and taking the time to help me.

Have a good evening,

Geoff.

LIST to TABS: Creating a copy of an existing tab for each name in a column

LinkBack

Thread Tools

Rate This Thread

Display

LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Re: LIST to TABS: Creating a copy of an existing tab for each name in a column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] creating tabs from a summary list of dates

Rename existing tabs from list on sheet 1

[SOLVED] Select Existing WS from Range List + Copy + Rename from List

Creating summary heading list on 1 tab from multiple tabs

[SOLVED] Is There A Way Of Creating List Of Al The Tabs

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions