Any way to autogenerate sheets from a list based on UNIQUE names in the list

[praveenvelath]

Hello Team,

I would greatly appreciate if anyone could help me on a macro. I have a number of populated rows in one sheet (Sheet A) and one of the columns contains a drop-down list. Now, I would like a macro to autogenerate sheets with the names of each sheet corresponding to the name on the list. HOWEVER, the catch is that the column in sheet A will end up with a couple of rows with duplicate names. In other words, if we consider the column to be J, J12 might be named 'Pipes', J13 could be 'Plates', and J14 could again be 'Pipes'.

Now, how do you think the following code could be tweaked in such a way that the macro gets executed without a hitch?

Sub CreateSheetsFromAList()
    Dim MyCell As Range, MyRange As Range
  
    Set MyRange = Sheets("SheetA").Range("J12")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Next MyCell
End Sub

Thanks a lot for your help on this.

BR,
Praveen

 

[Deepak]

Check it..

Sub CreateSheetsFromAList()
    Dim MyCell As Range, MyRange As Range
 
    Set MyRange = Sheets("SheetA").Range("J12")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        If Not Evaluate("ISREF('" & MyCell.Value & "'!A1)") Then _
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = MyCell.Value
    Next
End Sub

 

[praveenvelath]

Dear Deepak,

This is working perfectly! Thank you so much for your very prompt and accurate response.

Much appreciated!

Regards,
Praveen