Application-defined error - copy visable cells to new worksheet

[mssbass]

Anyone know why I'm getting a Run-time error '1004': Application-defined or object-defined error on this statement?

Sub FilterQueues()

Dim wscopy As Worksheet
Dim wsdest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Set wscopy = Workbooks("Star Ninja Tool 9.1_2.xlsm").Worksheets("Staging")
Set wsdest = Workbooks("Star Ninja Tool 9.1_2.xlsm").Worksheets("Staffing_Open_Report")
lCopyLastRow = wscopy.Cells(wscopy.Rows.Count, "B").End(xlUp).Offset(1).Row
lDestLastRow = wsdest.Cells(wsdest.Rows.Count, "B").End(xlUp).Offset(1).Row

wsdest.Range("A3:AF" & lDestLastRow).ClearContents

With wscopy
.Range("$A:$AF").AutoFilter field:=32, Criteria1:="N"
.Range("$A1:$AE" & lCopyLastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=wsdest.Range("B2")

End With

End Sub

 

[jasonb75]

Why are you copying the entire row when it appears that you only need columns A:AE?

Copying entire row and pasting to column B will cause an error because the last column of the entire row will fall off of the end of the sheet. It is also bad practice to copy entire rows or columns as it creates unnecessary bloat in the file.

Also you will get an error if there are no visible rows in the selected range.

 

[Fluff]

You cannot copy an entire row & then paste it in col B.
try

With wscopy
   .Range("$A:$AF").AutoFilter field:=32, Criteria1:="N"
   .AutoFilter.Range.Copy wsdest.Range("B2")
End With

 

[mssbass]

Worked perfectly - thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.