For Next loop debugging

[fklatecki]

Greetings Excel VBA gurus,

I need a another set of eyes on this VBA code. The hard part to admit was that a slightly previous version was working as desired.

I am Bursting a large Sheet into individual sheets based on value in the first column. (credit to Chandoo.org for basis of this VBA code)

The code runs as planned for the first pass, but on the second run I get a debug error. Can I be directed to correct the error of my ways?



Sub breakMyList() ' This macro takes values in the range myList
' and breaks it in to multiple lists
' and saves them to separate files.

Dim cell As Range
Dim curPath As String
Dim ws As Worksheet
Dim sShtName As String

Set ws = Sheets("SupplierSales")

curPath = ActiveWorkbook.Path & ""

Application.ScreenUpdating = True
Application.DisplayAlerts = False


For Each cell In Range("Suppliers")
Sheets("SupplierSales").Select

sShtName = Sheet1.Cells(4, 2).Value


Range("myList").AdvancedFilter Action:=xlFilterCopy, _
criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False
Range(Range("Extract"), Range("Extract").End(xlDown)).Copy


'adds worksheet
Worksheets.Add After:=Sheets(Worksheets.Count)


ActiveSheet.Name = sShtName <------------------------------debug error on second pass...That name is already taken. Try another one.

ActiveSheet.Paste

Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.CutCopyMode = False




Worksheets("SupplierSales").Activate
Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents



Next cell

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Misca]

Welcome to the message board!

Your problem seems to be the line

[COLOR=#333333]sShtName = Sheet1.Cells(4, 2).Value[/COLOR]

The name is static. Replace it with a dynamic name:

sshtname = Sheet1.Cells(4, 2).Value & Sheets.Count

 

[fklatecki]

I apologize for being in midst of my VBA learning curve.

The value of sShtName should be a single cell Range on Sheet(1) Label "SupplierSales", the Range name is "SheetName".

Your assistance is greatly appreciated...