Macro to add worksheets based on Cell Values

s0nicstang

Board Regular
Joined
Jan 7, 2009
Messages
73
I want to add new worksheets and name them based on the values of what i have in Col C on my main Tab.

This is what i came up with which will insert only the 1st one on the list?

With Sheets("Main")

Lr = .Range("C5").End(xlDown).Row
For i = 5 To Lr

Set ws = Range("C" & i)
Sheets("Main").Select
Worksheets.Add().Name = ws
Next i
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
With Sheets("Main")
LR = .Range("C" & Rows.Count).End(xlUp).Row
For i = 5 To LR
    Worksheets.Add().Name = .Range("C" & i).Value
Next i
End With
 

badinvestor

New Member
Joined
Dec 28, 2009
Messages
21
This will ensure that you don't re-create the same tab if it already exists.
Can modify to not copy the first row of data.

Public Sub MoveToTab()
Dim rngRow As Range
Dim rngCell As Range
On Error GoTo ErrHnd
'use All used rows except first, in 'Source' Worksheet
With Worksheets("Role Summary").UsedRange.Offset(3, 0) _
.Resize(Worksheets("Role Summary").UsedRange.Rows.Count - 1, _
Worksheets("Role Summary").UsedRange.Columns.Count)
'loop through all rows
For Each rngRow In .Rows
'test if tab exists
On Error Resume Next
If Not Worksheets(rngRow.Range("A1").Text).Name <> "" Then
On Error GoTo ErrHnd
'No Tab of this name - so create one and copy row
Worksheets.Add After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = rngRow.Range("A1").Text
rngRow.Copy Destination:=Worksheets(rngRow.Range("A1").Text).Range("A1")
Else
On Error GoTo ErrHnd
'worksheet exists
'copy row to end of used range
rngRow.Copy Destination:=Worksheets(rngRow.Range("a1").Text).Range("A1") _
.Offset(Worksheets(rngRow.Range("a1").Text).UsedRange.Rows.Count, 0)
End If
Next rngRow
End With
Exit Sub
'error handler
ErrHnd:
Err.Clear
End Sub
 

badinvestor

New Member
Joined
Dec 28, 2009
Messages
21
With regards to my previous post I have a question of my own the relates to this thread that I hope someone can help with. Using the macro below in addition to creating a tab for each list item I need the macro to then copy all of the information from a seperate worksheet named "Forms Template" and paste it into the newly created tab.
Any suggestions would be great!

This will ensure that you don't re-create the same tab if it already exists.
Can modify to not copy the first row of data.

Public Sub MoveToTab()
Dim rngRow As Range
Dim rngCell As Range
On Error GoTo ErrHnd
'use All used rows except first, in 'Source' Worksheet
With Worksheets("Role Summary").UsedRange.Offset(3, 0) _
.Resize(Worksheets("Role Summary").UsedRange.Rows.Count - 1, _
Worksheets("Role Summary").UsedRange.Columns.Count)
'loop through all rows
For Each rngRow In .Rows
'test if tab exists
On Error Resume Next
If Not Worksheets(rngRow.Range("A1").Text).Name <> "" Then
On Error GoTo ErrHnd
'No Tab of this name - so create one and copy row
Worksheets.Add After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = rngRow.Range("A1").Text
rngRow.Copy Destination:=Worksheets(rngRow.Range("A1").Text).Range("A1")
Else
On Error GoTo ErrHnd
'worksheet exists
'copy row to end of used range
rngRow.Copy Destination:=Worksheets(rngRow.Range("a1").Text).Range("A1") _
.Offset(Worksheets(rngRow.Range("a1").Text).UsedRange.Rows.Count, 0)
End If
Next rngRow
End With
Exit Sub
'error handler
ErrHnd:
Err.Clear
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top