none
Copy Active Sheet and Name it Based on Strings in an Array of Cells RRS feed

  • Question

  • This code keeps failing.

    Sub CreateSheetsFromAList()
        Dim MyCell As Range, MyRange As Range
         
        Set MyRange = Sheets("ISIN_List").Range("A1")
        Set MyRange = Range(MyRange, MyRange.End(xlDown))
    
        For Each MyCell In MyRange
            ActiveSheet.Copy After:=Sheets(Sheets.Count)
            'Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
            ActiveSheet.Name = MyCell.Value ' renames the new worksheet
        Next MyCell
    End Sub

    It keeps saying it can't rename a sheet the same name as an existing sheet.  That's fine.  I don't see how an existing sheet with the same name though.  ;)

    Maybe there is something wrong with the loop logic.  I guess I'm not seeing the real issue here.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, May 19, 2015 11:43 PM

Answers

  • You're right.  I guess I had some wackadoodle data in one of those cells and assigning that as a sheet name caused the error.  All set now.

    Thanks.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by L.HlModerator Tuesday, June 2, 2015 3:10 AM
    Thursday, May 21, 2015 12:21 AM

All replies

  • I can't seen anything wrong with it. Works fine under test for me. However, the code can only be run once.

    I left the Sheets.Add line commented out as I assume it was supposed to be.


    Regards, OssieMac

    Wednesday, May 20, 2015 5:03 AM
  • You're right.  I guess I had some wackadoodle data in one of those cells and assigning that as a sheet name caused the error.  All set now.

    Thanks.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by L.HlModerator Tuesday, June 2, 2015 3:10 AM
    Thursday, May 21, 2015 12:21 AM