Runtime error 1004 when copying and pasting a filtered collumn excel 2010

**Shedacq** · 08-14-2012, 04:37 AM

Hi i have a macro which worked in excel 2007 but since our company has changed to 2010 i doesnt seem to work anymore.
I keep getting runtime error 1004 "That command cannot be used on multiple selections"

This i get while running following macro part

  '' open del
    ThisWorkbook.Activate
    Sheets("Open Del").Select
    ActiveSheet.Range("$A$3:$E$1000").AutoFilter Field:=1, Criteria1:=pl_name
    Range("B3:E1000").Select
    Selection.Copy
    Workbooks(2).Activate
    Sheets("Open Del").Select
    ActiveSheet.Paste
'    Columns("E:E").Select
'    Range(Selection, Selection.End(xlToRight)).Select
'    Selection.Delete Shift:=xlToLeft

I get the error on the "ActiveSheet.Paste"

can someone please help me.

Thanks

**arlu1201** · 08-14-2012, 05:50 AM

You want only the filtered cells to be copied right?

**Shedacq** · 08-14-2012, 05:59 AM

Yes i do but it seems that doesnt work

EDIT

IT seems i found the easy solution thanks to your response it was just as simple as that just only selecting the range from B4 and not from B3 as B3 is filter header.

thanks

**arlu1201** · 08-14-2012, 06:35 AM

Try this

With ThisWorkbook.Worksheets("Open Del")
    .Range("$A$3:$E$1000").AutoFilter field:=1, Criteria1:="1"
    .UsedRange.SpecialCells(xlCellTypeVisible).Copy Workbooks(2).Worksheets("Open Del").Range("A1")
End With