When I run the following code, I keep getting an error: Run-time error '1004': Application-defined or object-defined error.
This is occurring when the value of MyCell goes from having content, in my case a product code like AB-1234 to a value of ""
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Summary").Range("A10")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets(9).Copy After:=Sheets(Sheets.Count) 'Create a new worksheet as a copy of Sheet number 9 in this example
Sheets(Sheets.Count).Name = MyCell.Value 'Renames the new worksheets