Application-defined error - copy visable cells to new worksheet

mssbass

Board Regular
Joined
Nov 14, 2002
Messages
235
Platform
  1. Windows
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,912
Office Version
  1. 365
Platform
  1. Windows
You cannot copy an entire row & then paste it in col B.
try
VBA Code:
With wscopy
   .Range("$A:$AF").AutoFilter field:=32, Criteria1:="N"
   .AutoFilter.Range.Copy wsdest.Range("B2")
End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,912
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,201
Messages
5,640,821
Members
417,168
Latest member
StumpoC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top