VBA - Adding a New Worksheet, Naming with Cell Reference in Source Sheet

[jcoleman5534]

Hello-

Here's what I'm trying to do....

I have ~2250 names in SheetA, Column A (Cells A1:A2250). I need VBA to add 2250 worksheets for each of the names in the Column. When it adds the new worksheet I need cell B7 in each of the new worksheets filled by with the names from cell A1 then A2 then A3 and so on for every sheet that is added. Additionally, it would be great to have the sheet names also equal cell B7.

 

[Smitty]

Welcome to the Board!

Here's one way:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> AddSheets()<br>****<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>****<br>********<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)<br>************Worksheets.Add After:=Sheets(Sheets.Count)<br>************<SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>****************.Name = c.Value<br>****************.Range("B7").Value = c.Value<br>************<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>********<SPAN style="color:#00007F">Next</SPAN> c<br>********<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note that the code doesn't take potential duplicates names into account, in which case it will bomb. If there can be duplicates, you'll need to indicate how you want to manage that.

HTH,

 

[jcoleman5534]

Hello-

Here's what I'm trying to do....

I have ~2250 names in SheetA, Column A (Cells A1:A2250). I need VBA to add 2250 worksheets for each of the names in the Column. When it adds the new worksheet I need cell B7 in each of the new worksheets filled by with the names from cell A1 then A2 then A3 and so on for every sheet that is added. Additionally, it would be great to have the sheet names also equal cell B7.

Ok, so if I want to perform the same function except applying formatting of a current sheet with the macro that adds the new sheets how do I implement?

 

[jcoleman5534]

In other words, I have a sheet that is formatted a certain way that I just want to create copies of with the new list of names input rather than simply copied.

Does that make sense?

 

[Smitty]

In other words, I have a sheet that is formatted a certain way that I just want to create copies of with the new list of names input rather than simply copied.

Does that make sense?

You can use:

Sheets("Master").Copy After:=Sheets(Sheets.Count)

Just note that there is a known bug in Excel that it will hiccup after programmatically copying x number of sheets. Microsoft documents a workaround here: Copying worksheet programmatically causes run-time error 1004 in Excel

 

[jcoleman5534]

So where do I insert that into the original code?

You can use:

Sheets("Master").Copy After:=Sheets(Sheets.Count)

Just note that there is a known bug in Excel that it will hiccup after programmatically copying x number of sheets. Microsoft documents a workaround here: Copying worksheet programmatically causes run-time error 1004 in Excel

 

[Smitty]

Replace the Worksheets.Add portion with it.

Note that you'll need to change the "Master" sheet name as it relates to your workbook.