Sheet naming from the cell.

[anee_4285]

Hello All,

I have the names in the Columns in a sheet1. I want new sheets should be created with the name of cell in the row.

The name should be like this.

Sheet1-aaaa
sheet2-bbbb
sheet3-cccc

I cant each sheet because i have 150 rows.
My request is it should dected how many names it have and have to create a 150 sheets with name in the cell.

aaaa
bbbb
cccc
dddd

<tbody>
</tbody>

Regards,
Anil

 

[Gary's Student]

With your names in Sheet1, run this short macro:

Sub SheetMaker()
    Dim N As Long, i As Long, ary
    
    With Sheets("Sheet1")
        N = .Cells(Rows.Count, "A").End(xlUp).Row
        ReDim ary(1 To N) As String
        For i = 1 To N
            ary(i) = .Cells(i, 1).Value
        Next i
    End With
    
    For i = 1 To N
        ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = "Sheet" & i & "-" & ary(i)
    Next i
End Sub

 

[anee_4285]

With your names in Sheet1, run this short macro:

Sub SheetMaker()
    Dim N As Long, i As Long, ary
    
    With Sheets("Sheet1")
        N = .Cells(Rows.Count, "A").End(xlUp).Row
        ReDim ary(1 To N) As String
        For i = 1 To N
            ary(i) = .Cells(i, 1).Value
        Next i
    End With
    
    For i = 1 To N
        ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = "Sheet" & i & "-" & ary(i)
    Next i
End Sub

Thanks a lot.

Can i know the process to run this Macro?
In sheet1, right click, View code, select work sheet and pate it... is it right?
but nithing is happening

Regards,
Anil

 

[anee_4285]

its working now but every sheet have name is like this
sheet1-aaaa
sheet2-bbbb

it should only be
aaaa
bbbb

Regards,
Anil

 

[anee_4285]

Thx a lot. its working now...

Now can i have the all sheet name in the rescpective A cell. So it will be easy for me to search.

Example:
Sheet 1:
Name: aaaa
In sheet 1 A cell it should be aaaa

Sheet 2:
Name bbbbb
In sheet 2 A cell it should be bbbb