copy and paste error
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 *************************** |
copy and paste error
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 *************************** |
copy and paste error
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 *************************** |
All times are GMT +1. The time now is 01:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com