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
***************************

Hi,

Instead you can use more simple method of copying and pasting filtered data
as below
in the menubar EditgotoSpecialTick the visible cells only radio button
and you can copy and paste the filtered data.
--
Thanks
Suleman Peerzade


"jenny" wrote:

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
***************************

try this

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jenny" wrote in message
...
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
***************************