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