VBA Macro - Convert the "Excel Sheet Column" name into the "new tab"

[fahadalambd]

Hi everyone,

Hope you guys are well. I need a VBA Macro Script which will automatic convert the "Excel Sheet Column" name into the "new tab".

For example, I have 5 columns - column1, column2, column3, column4, column5. I need a VBA script which will convert these columns into the 5 new tab.

Please check the attached screen-shot.

Thanks in advance.

 

[My Aswer Is This]

Hi everyone,

Hope you guys are well. I need a VBA Macro Script which will automatic convert the "Excel Sheet Column" name into the "new tab".

For example, I have 5 columns - column1, column2, column3, column4, column5. I need a VBA script which will convert these columns into the 5 new tab.

Please check the attached screen-shot.

Thanks in advance.

I believe you mean you have 5 cells in column A
And want a new sheet made with the values in Range("A1:A5")
Is that correct?

 

[Michael M]

Maybe this way...assuming your sheet with the names is called "Sheet1"....change to suit

Sub MM1()
Dim r As Long
For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    Worksheets.Add
    ActiveSheet.Name = Sheets("Sheet1").Range("A" & r).Value
Next r
End Sub

 

[My Aswer Is This]

Try this:

'Modified 11/2/2022  9:00:21 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim sn As String

Dim Lastrow As Long
Dim ans As String
ans = ActiveSheet.Name
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        sn = Sheets(ans).Cells(i, 1).Value
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = sn
    Next
    Application.Goto Sheets(ans).Range("A1")

Application.ScreenUpdating = True
End Sub

 

[Fluff]

Another option

Sub fahadalambd()
   Dim Cl As Range
   
   Application.ScreenUpdating = False
   With ActiveSheet
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
            Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value
         End If
      Next Cl
      .Select
   End With
End Sub

 

[fahadalambd]

Try this:

'Modified 11/2/2022  9:00:21 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim sn As String

Dim Lastrow As Long
Dim ans As String
ans = ActiveSheet.Name
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        sn = Sheets(ans).Cells(i, 1).Value
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = sn
    Next
    Application.Goto Sheets(ans).Range("A1")

Application.ScreenUpdating = True
End Sub

Thank you so much buddy. IT is working. Thanks for your help

 

[fahadalambd]

Another option

Sub fahadalambd()
   Dim Cl As Range
  
   Application.ScreenUpdating = False
   With ActiveSheet
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
            Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value
         End If
      Next Cl
      .Select
   End With
End Sub

Thank you so much. You save my life. Thanks again

 

[fahadalambd]

Maybe this way...assuming your sheet with the names is called "Sheet1"....change to suit

Sub MM1()
Dim r As Long
For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    Worksheets.Add
    ActiveSheet.Name = Sheets("Sheet1").Range("A" & r).Value
Next r
End Sub

Maybe this way...assuming your sheet with the names is called "Sheet1"....change to suit

Sub MM1()
Dim r As Long
For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    Worksheets.Add
    ActiveSheet.Name = Sheets("Sheet1").Range("A" & r).Value
Next r
End Sub

Thank you so much mate. You are so genius. Really appreciate for your help

 

[Fluff]

Glad we could help & thanks for the feedback.