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

 

[tbwalter]

So what if instead of using a "named range", I used a sheet name/cell name? In this case, it would be Sheet Name: Cabinet, Cells: A1:G98. I thought that using a named range would be easier, but I guess not. I can delete the range name without issue. Would that be easier?

 

[SQUIDD]

Hi.
Ok, so your named range you are wanting to copy, is it just the values from the named range you want to put into the new sheet.

if so, using the named range should be fine.

but I cannot quite understand what you require.

Is this code part of the code I previously wrote for you. And you want the named range values in each new worksheet.
Or
Is this a bee code where you want to add 1new sheet with the named range values in it ?

 

[tbwalter]

This would be an addition to the code you wrote. I was hoping to just add a copy line, but apparently I'm not enough on the ball. I really appreciate your help here. Again, it doesn't have to be a named range. I just created a template on one tab (Sheet Name: Cabinet, Cells: A1:G98), and want it copied when the new tabs are created.

 

[SQUIDD]

You you want a code that does the following.

1. Creates a new tab.
2. Copies your range to the new tab.



Dave

 

[tbwalter]

You you want a code that does the following.

1. Creates a new tab.
2. Copies your range to the new tab.



Dave

Correct

 

[tbwalter]

I got it! I added: ThisWorkbook.Sheets("Cabinet").Range("A1:G100").EntireRow.Copy ActiveSheet.Range("A1") to your code, and it works like a charm. The final code is:

Sub add_tabs()

' This code was written by SQUIDD @ MREXCEL.COM

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
ThisWorkbook.Sheets("Cabinet").Range("A1:G100").EntireRow.Copy ActiveSheet.Range("A1")
Next B
Next A
Sheets(STARTSHEET).Activate
Application.ScreenUpdating = True
End Sub

 

[SQUIDD]

Well done, im glad you sorted that out, and its nice feeling when you work it out yourself.