Adding and Naming Multiple Worksheets
 

I am looking for an efficient way to add and name multiple worksheets. I
have a sheet with multiple categories which I must then split into new
worksheets. Any Ideas would be appreciated.

One way would be to use a macro. If you have a unique list of categories in
column A then something like this should work:

Sub lime()
Dim i As Long
Dim ws As Worksheet
Dim tSht As Worksheet
On Error Resume Next
Set tSht = ActiveSheet
For i = 1 To tSht.Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Sheets.Add
ws.Name = tSht.Cells(i, 1).Value
Set ws = Nothing
Next i
On Error GoTo 0
End Sub

Hope this helps
Rowan

"Byron" wrote:

I am looking for an efficient way to add and name multiple worksheets. I
have a sheet with multiple categories which I must then split into new
worksheets. Any Ideas would be appreciated.

Thanks Rowan,

I am looking for a similar formula/macro to add worksheets depending on
content of first column in first sheet. I want to create one sheet per row
and transfer row content into the column in respective worksheets.

what modification I should do to get the above result.

Thansk & regard
mubeen

"Rowan" wrote:

One way would be to use a macro. If you have a unique list of categories in
column A then something like this should work:

Sub lime()
Dim i As Long
Dim ws As Worksheet
Dim tSht As Worksheet
On Error Resume Next
Set tSht = ActiveSheet
For i = 1 To tSht.Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Sheets.Add
ws.Name = tSht.Cells(i, 1).Value
Set ws = Nothing
Next i
On Error GoTo 0
End Sub

Hope this helps
Rowan

"Byron" wrote:

I am looking for an efficient way to add and name multiple worksheets. I
have a sheet with multiple categories which I must then split into new
worksheets. Any Ideas would be appreciated.

Hi Mubeen

Something like this should work:

Sub lime()
Dim i As Long
Dim ws As Worksheet
Dim tSht As Worksheet
On Error Resume Next
Set tSht = ActiveSheet
For i = 1 To tSht.Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Sheets.Add
ws.Name = tSht.Cells(i, 1).Value
tSht.rows(i).copy ws.cells(1,1) '<added line
Set ws = Nothing
Next i
On Error GoTo 0
End Sub

Regards
Rowan

Mubeen wrote:
Thanks Rowan,

I am looking for a similar formula/macro to add worksheets depending on
content of first column in first sheet. I want to create one sheet per row
and transfer row content into the column in respective worksheets.

what modification I should do to get the above result.

Thansk & regard
mubeen

"Rowan" wrote:


One way would be to use a macro. If you have a unique list of categories in
column A then something like this should work:

Sub lime()
Dim i As Long
Dim ws As Worksheet
Dim tSht As Worksheet
On Error Resume Next
Set tSht = ActiveSheet
For i = 1 To tSht.Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Sheets.Add
ws.Name = tSht.Cells(i, 1).Value
Set ws = Nothing
Next i
On Error GoTo 0
End Sub

Hope this helps
Rowan

"Byron" wrote:


I am looking for an efficient way to add and name multiple worksheets. I
have a sheet with multiple categories which I must then split into new
worksheets. Any Ideas would be appreciated.

Thank you very much Rowan,

I really apprciate.

Thanks once again.
mubeen

"Rowan" wrote:

Hi Mubeen

Something like this should work:

Sub lime()
Dim i As Long
Dim ws As Worksheet
Dim tSht As Worksheet
On Error Resume Next
Set tSht = ActiveSheet
For i = 1 To tSht.Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Sheets.Add
ws.Name = tSht.Cells(i, 1).Value
tSht.rows(i).copy ws.cells(1,1) '<added line
Set ws = Nothing
Next i
On Error GoTo 0
End Sub

Regards
Rowan

Mubeen wrote:
Thanks Rowan,

I am looking for a similar formula/macro to add worksheets depending on
content of first column in first sheet. I want to create one sheet per row
and transfer row content into the column in respective worksheets.

what modification I should do to get the above result.

Thansk & regard
mubeen

"Rowan" wrote:


One way would be to use a macro. If you have a unique list of categories in
column A then something like this should work:

Sub lime()
Dim i As Long
Dim ws As Worksheet
Dim tSht As Worksheet
On Error Resume Next
Set tSht = ActiveSheet
For i = 1 To tSht.Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Sheets.Add
ws.Name = tSht.Cells(i, 1).Value
Set ws = Nothing
Next i
On Error GoTo 0
End Sub

Hope this helps
Rowan

"Byron" wrote:


I am looking for an efficient way to add and name multiple worksheets. I
have a sheet with multiple categories which I must then split into new
worksheets. Any Ideas would be appreciated.

Thanks for the input. Looks like it's going to work.

"Mubeen" wrote:

Thank you very much Rowan,

I really apprciate.

Thanks once again.
mubeen

"Rowan" wrote:

Hi Mubeen

Something like this should work:

Sub lime()
Dim i As Long
Dim ws As Worksheet
Dim tSht As Worksheet
On Error Resume Next
Set tSht = ActiveSheet
For i = 1 To tSht.Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Sheets.Add
ws.Name = tSht.Cells(i, 1).Value
tSht.rows(i).copy ws.cells(1,1) '<added line
Set ws = Nothing
Next i
On Error GoTo 0
End Sub

Regards
Rowan

Mubeen wrote:
Thanks Rowan,

I am looking for a similar formula/macro to add worksheets depending on
content of first column in first sheet. I want to create one sheet per row
and transfer row content into the column in respective worksheets.

what modification I should do to get the above result.

Thansk & regard
mubeen

"Rowan" wrote:


One way would be to use a macro. If you have a unique list of categories in
column A then something like this should work:

Sub lime()
Dim i As Long
Dim ws As Worksheet
Dim tSht As Worksheet
On Error Resume Next
Set tSht = ActiveSheet
For i = 1 To tSht.Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Sheets.Add
ws.Name = tSht.Cells(i, 1).Value
Set ws = Nothing
Next i
On Error GoTo 0
End Sub

Hope this helps
Rowan

"Byron" wrote:


I am looking for an efficient way to add and name multiple worksheets. I
have a sheet with multiple categories which I must then split into new
worksheets. Any Ideas would be appreciated.

What if I had a column of a hundred or so entries that had 8 or 9 of
categories in it and wanted to have one worksheet that represents of each
category. Ideas?

"Rowan" wrote:

One way would be to use a macro. If you have a unique list of categories in
column A then something like this should work:

Sub lime()
Dim i As Long
Dim ws As Worksheet
Dim tSht As Worksheet
On Error Resume Next
Set tSht = ActiveSheet
For i = 1 To tSht.Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Sheets.Add
ws.Name = tSht.Cells(i, 1).Value
Set ws = Nothing
Next i
On Error GoTo 0
End Sub

Hope this helps
Rowan

"Byron" wrote:

I am looking for an efficient way to add and name multiple worksheets. I
have a sheet with multiple categories which I must then split into new
worksheets. Any Ideas would be appreciated.