Sheet not Copying Over

[nniedzielski]

I have the below macro, and on the line in red, the sheet will not copy. I am getting an error that says subscript is out of range, i cant seem to get it to copy.

Worksheets(xCar).Copy After:=Worksheets("Sheet1")

Sub loopFilter()

Sheets("Data").Select

Dim erow As Long
erow = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row

Range("K1:K" & erow).Copy
Range("AD1:AD" & erow).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

ActiveSheet.Range("$AD$1:$AD$10000").RemoveDuplicates Columns:=1, Header:=xlYes

Dim ArrayDictionaryofItems As Object, Items As Variant, i As Long, Item As Variant
Dim x As Double

Set ArrayDictionaryofItems = CreateObject("Scripting.Dictionary")

With ActiveSheet

    If Not .AutoFilterMode Then .UsedRange.AutoFilter
    If .Cells.AutoFilter Then .Cells.AutoFilter

    Items = .Range(.Range("AD2"), .Cells(Rows.Count, "AD").End(xlUp))
        For i = 1 To UBound(Items, 1)
            ArrayDictionaryofItems(Items(i, 1)) = 1
        Next i
   
                x = 2
                    For Each xCar In ArrayDictionaryofItems.keys
                    Sheets("Data").UsedRange.AutoFilter field:=11, Criteria1:=xCar
                    Sheets.Add(Before:=Sheets(1)).Name = xCar
                    Sheets("Data").Select
                    Range("A1" & ":" & "Y" & erow).SpecialCells(xlCellTypeVisible).Copy
                    Sheets(xCar).Range("A1" & ":" & "Y1").PasteSpecial Paste:=xlPasteAll
                    Application.CutCopyMode = False
                   
                    'create attachment
                    Dim sFilename As String
                    sFilename = ThisWorkbook.Path & "\SCACMacro.xlsx"
                   
                    ' Add a new workbook
                    Dim wkTemp As Workbook
                    Set wkTemp = Workbooks.Add
                   
                    ' Copy the worksheet and delete the default
                    [COLOR=rgb(184, 49, 47)]Worksheets(xCar).Copy After:=Worksheets("Sheet1")[/COLOR]
                   
                    ' turn off alerts before delete worksheet
                    Application.DisplayAlerts = False
                    wkTemp.Worksheets(1).Delete
                    Application.DisplayAlerts = True
                   
                    ' Save the new workbook
                    wkTemp.SaveAs sFilename
                    wkTemp.Close
                   
                x = x + 1
            Next xCar

End With

End Sub

 

[Marc L]

As the active workbook is the new workbook so not the workbook where the sheet xcar is located​

so you must add the workbook reference before the xcar sheet reference …​

​

 

[jasonb75]

Unless I'm reading it wrong, there are no items in the dictionary.

Items = .Range(.Range("AD2"), .Cells(Rows.Count, "AD").End(xlUp))

The keyword, 'Items' is not qualified with a reference to the dictionary, which would make it nothing more than an undeclared variable.

 

[nniedzielski]

Unless I'm reading it wrong, there are no items in the dictionary.

Items = .Range(.Range("AD2"), .Cells(Rows.Count, "AD").End(xlUp))

The keyword, 'Items' is not qualified with a reference to the dictionary, which would make it nothing more than an undeclared variable.

the dictionary portion is working fine, everything is working solid until the trouble line

 

[jasonb75]

In that case, see post 2.

 

[nniedzielski]

​

As the active workbook is the new workbook so not the workbook where the sheet xcar is located​

so you must add the workbook reference before the xcar sheet reference …​

​

and how would that look?

 

[jasonb75]

Something like this

Workbooks("Book1").Worksheets(xCar).Copy After:=Worksheets("Sheet1")

Or you could declare a variable as a workbook at the start of the procedure and set that as the source workbook.

Dim wb As Workbook
Set wb = ActiveWorkbook


wb.Worksheets(xCar).Copy After:=Worksheets("Sheet1")

 

[Marc L]

​

Or using a worksheet variable for the xcar sheet …​

 

[jasonb75]

Or using a worksheet variable for the xcar sheet …

I think that would still need to refer back to the original workbook, otherwise it will error on the next iteration when the "Data" sheet is not found.
In theory when the new workbook is closed, it should revert back to the correct one but maybe better to err on the side of caution.

 

[Marc L]

Nope ! As a worksheet variable is obviously linked to its workbook or the Parent statement could not work …​

​

Another way : according to the poor initial post elaboration - near to a guessing challenge ! - if the sheet xcar is located in the same workbook​

of the worksheet in the codeline With ActiveSheet then this codeline should work : .Parent.Sheets(xcar).Copy …​