How can use COPY & PASTE filtered cells?

[apravato]

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?

 

[vconfused]

Can you sort to get the desired rows next to each other?

 

[erik.van.geit]

Hi, apravato,

you can use code for this

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

 

[apravato]

How do I apply that code?

 

[apravato]

Anyone know how to COPy & PASTE filtered cells?

 

[erik.van.geit]

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