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