How can use COPY & PASTE filtered cells?

apravato

New Member
Joined
Jan 20, 2005
Messages
8
Everytime I try it doesn't allow me, and says to use a single selection. I don't understand why it can't just copy the data into the approriate matching cells. I am using Auto-filtered cells. So if I have 1000 lines to my file and I filtered 5 of those lines, and want to copy and paste 5 lines from column D to the same 5 lines in column A, it doesn't let me. My 5 lines may be line 12, 234, 1400, 1406, and 2345. Etc... Anyone know how to make this happen?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, apravato,

you can use code for this

Code:
Sub copy_visible_cells()
sourcecol = 4
targetcol = 1
For Each ccc In Range(Cells(1, sourcecol), Cells(65536, sourcecol).End(xlUp)).SpecialCells(xlCellTypeVisible)
ccc.Copy Cells(ccc.Row, targetcol)
Next ccc
End Sub

kind regards,
Erik
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello,

Since you can't paste non-adjacent ranges the way you want it looks to me the best solution to yse a macro.
Paste the code in a module. It seems to me you didn't ever do that. It would be good to experiment a bit with the macro recorder. (tools/ macro/ recrod macro) Be curious and play around, that's the way the must of us learned it.

start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the code.

1. "step" through it with the function key F8
2. click on an item and hit F1 to let popup the help
3. use the macro recorder to see how code is generated (such code will need some "cleaning" afterwards)

kind regards,
Erik
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,021
Latest member
naimathulla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top