'Select method of worksheet class failed' mid loop

[filido]

Hello,

My code should filter each value and copy charts to different sheets. I looped this but it breaks mid loop. It works fine with 8 first values/sheets (should be 12 or so). I get error 1004: Select method of worksheet class failed. I highlighted the part in red when the error occurs.


Sub CreateCharts()


Dim wsDATA, wsPIA As Worksheet
Dim loData As ListObject
Dim erow As Long

Set wsDATA = Sheet2
Set wsPIA = Sheet4
Set loData = Sheet2.ListObjects("Table1")


wsDATA.Select
ActiveSheet.Columns("D").Select
Selection.Copy

wsDATA.Columns("T").Select
ActiveSheet.Paste
ActiveSheet.Range("$T$1:$T$100000").RemoveDuplicates Columns:=1, Header:=xlYes


Dim ArrayDictionaryofItems As Object, Items As Variant, i As Long, Item As Variant
Set ArrayDictionaryofItems = CreateObject("Scripting.Dictionary")


With ActiveSheet


'create list of unique items in column D that get filled into ArrayDictionaryofItems
Dim ui As Double

If Range("D3").Value <> "" Then
ui = 2
Items = Range(.Range("T2"), .Cells(Rows.Count, "T").End(xlUp))


'fills ArrayDictionaryofitems to the UBOUND (max) count of unique items in column T.

For i = 1 To UBound(Items, 1)
ArrayDictionaryofItems(Items(i, 1)) = 1
Next

Else

Item = Range("D2").Value
ui = 1
End If


'Filter multiple items if ui is set to equal 2 because D3 is blank
If ui = 2 Then

For i = 4 To UBound(Items, 1)
Sheets.Add after:=Sheets(i)
Next i

wsDATA.Select

Dim x As Double
x = 5

For Each Item In ArrayDictionaryofItems.keys
erow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
'autofilter on column D with this driver
loData.Range.AutoFilter Field:=4, Criteria1:=Item

wsPIA.Select
ActiveSheet.Shapes("Sales").CopyPicture
Sheets(x).Select
Columns("A").Select
ActiveSheet.Paste

wsPIA.Select
ActiveSheet.Shapes("Quantity").CopyPicture
Sheets(x).Select
Columns("U").Select
ActiveSheet.Paste

wsPIA.Select
ActiveSheet.Shapes("Customer").CopyPicture
Sheets(x).Select
Columns("AO").Select
ActiveSheet.Paste

wsDATA.Select

x = x + 1
Next Item
GoTo LINE99:
End If


'filter a single item in column since D3 is blank and there is only one item in column D to filter
If ui = 1 Then

Sheets.Add after:=ActiveSheet
wsDATA.Select

Item = Range("D2").Value
.UsedRange.AutoFilter Field:=4, Criteria1:=Item
End If

wsPIA.Select
ActiveSheet.Shapes("Sales").CopyPicture
Sheets(2).Select
Columns("A").Select
ActiveSheet.Paste

wsPIA.Select
ActiveSheet.Shapes("Quantity").CopyPicture
Sheets(2).Select
Columns("U").Select
ActiveSheet.Paste

wsPIA.Select
ActiveSheet.Shapes("Customer").CopyPicture
Sheets(2).Select
Columns("AO").Select
ActiveSheet.Paste


End With

LINE99:
With ActiveSheet
If .AutoFilterMode Then .UsedRange.AutoFilter
End With


End Sub

 

[filido]

I figured this out by myself.

For i = 4 To UBound(Items, 1)
Sheets.Add after:=Sheets(i)
Next i

should be

For i = 0 To UBound(Items, 1)
Sheets.Add after:=Sheets(i+4)
Next i

in the upper line the macro created i - 4 sheets (in this case i = 12 so it created 12 - 4 = 8 sheets) so x did not find the next sheet to paste after going through the first 8 sheets.