Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy error with Autofilter
I'm getting a Run-time error '1004' Copy method of Range Class faile
when I run the code below. (i.e. at the rng.copy line) My data starts in Column B and I want the column headings copied to th destination sheet too. The correct data is copied to the destinatio sheet before the error occurs. I've used (and slightly modified) Tom Ogilvy's code - which is ofte quoted on this forum - so I pretty much know its something I've done! I'm rather a newbie to VBA I'm afraid... Using Excel 2003 Sub aaddAutofileterdData() 'by Tom Ogilvy (mostly) Dim rng As Range Dim rng2 As Range If Not Worksheets("Data").AutoFilterMode Then Worksheets("Data").Range("B1:AB1").AutoFilter End If Sheets("Data").Activate With ActiveSheet.AutoFilter.Range Selection.AutoFilter Field:=11, Criteria1:="Marketing" Selection.AutoFilter Field:=12, Criteria1:="Cluster" On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng2 Is Nothing Then MsgBox "No data to copy" Else Worksheets("Mkting Clusters").Cells.Clear Set rng = ActiveSheet.AutoFilter.Range rng.Copy _ Destination:=Worksheets("Mkting Clusters").Range("A1") End If ActiveSheet.ShowAllData End Sub Any help would be greatly appreciated Thanks Jonatha -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Autofilter Error | Excel Discussion (Misc queries) | |||
Excel Autofilter error | Excel Discussion (Misc queries) | |||
AUTOFILTER copy and paste | Excel Programming | |||
autofilter macro causes #VALUE! error | Excel Programming | |||
VBA Autofilter error in '97, not 2000 | Excel Programming |