rename sheets according to column A names.

[fatalcore]

Hi,
I have a excel file with product names

I want a macro that will auto generate sheets based on the names of Column A that I select and will name then accordingly.

I am uploading a sheet for reference. In the example file all my names are in Column A of the tab DATA , and I have all the sheets with the names,
I want the macro in such a way that I select the names and run the macro and that many sheets will be generate with that name.

Thanks and regards,
Me

[BigBas]

Hi fatalcore,

Try the following code and report back. As it is, the code will create a new, blank worksheet and rename it based on the text within the active, selected cell. I don't have Excel 2007 so I couldn't test against your file, but it should work.

Sub AddSht()
    Dim shtName As String
    Dim sht As Worksheet
    
    shtName = ActiveCell.Value
    
    On Error Resume Next
        Set sht = Sheets(shtName)
        
        If Not sht Is Nothing Then
            MsgBox "Sheet " & shtName & " already exists"
            On Error GoTo 0
        Else
            Worksheets.Add().Name = shtName
        End If
    
End Sub

[fatalcore]

Hi bigbas,
The code works ok ...but it only adds one sheet based on selected cell but not cells(as you mentioned in your post).
can it be a bit tailored made to work on selected CELLS ? That will do my job.
thanks in advance.

[BigBas]

Sorry, I misread. Try the following code and report back.

Sub AddSht()
    Dim shts As Range
    Dim r As Range
    Dim sht As Worksheet
    
    Set shts = Selection
    
    For Each r In shts
        On Error Resume Next
            Set sht = Sheets(r.Value)
        
            If Not sht Is Nothing Then
                MsgBox "Sheet " & shtName & " already exists"
                On Error GoTo 0
                
            Else
                Worksheets.Add().Name = r
            End If
    Next r
    
End Sub

[fatalcore]

Hi bigbass.
It's working great...but it's creating the pages back side..I mean on the left side instead of creating on the right side....It will be great if the pages generate on the right side sequentially.
thanks in advance.

[BigBas]

Hi fatalcore,

Try this code and report back

Sub AddSht()
    Dim shts As Range
    Dim r As Range
    Dim sht As Worksheet
    
    Set shts = Selection
    
    For Each r In shts
        On Error Resume Next
            Set sht = Sheets(r.Value)
        
            If Not sht Is Nothing Then
                MsgBox "Sheet " & shtName & " already exists"
                On Error GoTo 0
                
            Else
                Worksheets.Add(after:=Sheets(Sheets.Count)).Name = r
            End If
    Next r
    
End Sub

[fatalcore]

Perfect...
Thanks bro...
I am marking the trhead as complete...

[BigBas]

Not a problem fatal. Glad the solution helped.