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.