VBA Code to Create Individually Named Tabs

[tbwalter]

I've been struggling with this idea for a while. I want to take the below table and add a button that results in individually named tabs. In the example below, after hitting the "Generate Tabs" button, there would be 5 new tabs: Site A-1, Site A-2, Site A-3, Site B-1, and Site C-1. The site names would be variables, and blanks would be treated as zeros. Any help you could provide would be greatly appreciated.

Colocation Order Form Content.xlsm
	A	B	C	D	E	F	G	H
16	Site #	Location	Half	Full	Cage
17	1	Site A	1	2	0
18	2	Site B	0	0	1
19	3	Site C	1	0	0
20	4
21	5
22	6
23	7
24	8						Generate Tabs
25	9
26	10
27
Start Here

 

[SQUIDD]

Hi

So i do not follow how you want to make up your tab names.
for instance, is the 1st tabe name "Site A-1" because we can use cells B17 and cells C1?
and if the above is correct, then where did you get "Site A-3" from??

is your data set larger than what you are showing, so you need it more dynanic?

dave

 

[tbwalter]

Hi

So i do not follow how you want to make up your tab names.
for instance, is the 1st tabe name "Site A-1" because we can use cells B17 and cells C1?
and if the above is correct, then where did you get "Site A-3" from??

is your data set larger than what you are showing, so you need it more dynanic?

dave

The number of tabs for Site A (dynamic name) would be cells C17 + D17 + E17. So if the Site Name were, for example, Seattle, the tabs would be called Seattle 1, Seattle 2, and Seattle 3. Column A and row 16 are not part of the equation. Hope that helps.

 

[SQUIDD]

ok

but E17 is a "0", so where to we get the 3 from. Since there are 0's in row 18 and 19.

 

[tbwalter]

ok

but E17 is a "0", so where to we get the 3 from. Since there are 0's in row 18 and 19.

The number of tabs is based on row. So using the example above (Site A = Seattle), 1 "half" and 2 "full" = 3 tabs. If it helps, I'm referring to Data Center Cabinets. So row 18 would be a different location (let's just say Portland). In this case Portland is getting neither a Half nor Full Cabinet, but a Cage space, so the new tab for that site would "Portland 1". Site C is getting 1 Half Cab, bringing the total new tab count to 5: Seattle 1, Seattle 2, Seattle 3, Portland 1, and Site C 1. All new tabs created by clicking the "Generate Tabs" button.

 

[SQUIDD]

ok, sorry, i missed the addition for the row.

Does the below suit you requirments. Hopefully i understood you properly.

test it on some dummy data 1st

add a button and assign the macro

Sub add_tabs()
Application.ScreenUpdating = False
STARTSHEET = ActiveSheet.Name
LR = Range("B" & Rows.Count).End(xlUp).Row
    For A = 17 To LR
        Sheets(STARTSHEET).Activate
        Total = Application.WorksheetFunction.Sum(Range("C" & A & ":E" & A))
        For B = 1 To Total
            Sheets(STARTSHEET).Activate
            Sheets.Add.Name = Range("B" & A) & "-" & B
        Next B
    Next A
Sheets(STARTSHEET).Activate
Application.ScreenUpdating = True
End Sub

 

[tbwalter]

ok, sorry, i missed the addition for the row.

Does the below suit you requirments. Hopefully i understood you properly.

test it on some dummy data 1st

add a button and assign the macro

Sub add_tabs()
Application.ScreenUpdating = False
STARTSHEET = ActiveSheet.Name
LR = Range("B" & Rows.Count).End(xlUp).Row
    For A = 17 To LR
        Sheets(STARTSHEET).Activate
        Total = Application.WorksheetFunction.Sum(Range("C" & A & ":E" & A))
        For B = 1 To Total
            Sheets(STARTSHEET).Activate
            Sheets.Add.Name = Range("B" & A) & "-" & B
        Next B
    Next A
Sheets(STARTSHEET).Activate
Application.ScreenUpdating = True
End Sub

Pure genius. Thank you! This has been bugging me for literally weeks. You are my hero.

 

[SQUIDD]

glad to help

 

[tbwalter]

glad to help

Follow up question: I'm trying to copy a named range into the new worksheets before creating the next worksheet. The named range is "Cabinet"
The code I used is: Range("Cabinet").Copy Worksheets(ActiveSheet).Range("Cabinet"), but it's the ActiveSheet that's failing, and I can't use the "Sheet2" or whatever because I don't know what the new sheet name will be in advance. Any thoughts?

 

[SQUIDD]

Would it be a solution for you if

1, we created a new sheet in code
2, we obtain the name of the created sheet.
3, then copy and paste the named range

BUT

I dont think you can have 2 named ranges in a workbook with the same name?
Are you planning to delete the named range?

By the way, you can add named ranges in code also, so why copy a named range at all, just create it?

dave