copy and paste error

[jenny]

I am tryng to copy and paste after filter using the below macro. However, it
prompt me an error stating (total no of rows: >30K, after filtered: >15K):
"Microsoft Office Excel cannot create or use the data range reference
because it is too complex...."
Even when I put in the "On error resume next", my range data selection is
blank and I copy and paste empty data.
*********My macro***********
If .Cells(1, Colcount) = "KEYFIG" Then
.Cells(1, 1).AutoFilter Field:=Colcount, Criteria1:="OPT RDP"
With .AutoFilter.Range
On Error Resume Next
Set Filterrg = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
Filterrg.Copy
End With
End If
***************************

 

[Suleman Peerzade]

Hi,

Instead you can use more simple method of copying and pasting filtered data
as below
in the menubar Edit>>goto>>Special>>Tick the visible cells only radio button
and you can copy and paste the filtered data.

 

[Don Guillett]

try this

lr=cells(rows.count,1).END(XLUP).ROW-1
..Offset(1).Resize(lr).SpecialCells(xlCellTypeVisible).copy