Paste to visible cells only

[lichfields]

I'm trying to copy data from column A in a filtered list to the next column B. As column B is of course also filtered, I want to copy the visible cells (no problem) but then paste them to the visible cells (problem).

I've checked through earlier questions on this subject to find an answer but it seems there isn't one - at least not a straightforward one.

I did find a possible solution in the form of a "Code" as follows:

Worksheets("Sheet Name").Activate
Range("A2").Select
Selection.Copy
Range("A3:N1000").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Is this a macro thing? If so, how do you use it? I'm afraid I'm a macro virgin

Can anyone help?

 

[GorD]

How about selecting visible cells only before pasting - would that work.

Select your range in Col B, then shortcut to visible cells only is ALT;

(long way is edit->goto>special>visible cells only), then paste.

Does that do it

 

[Norie]

I don't think this is possible without code.

 

[lichfields]

Thanks, folks

I did try as you suggested but, as I pretty much expected from earlier responses, I got an error message:

"The comand you chose cannot be performed with multiple sections. Select a single range and click the command again" (which defeats the object)

Can anyone tell me how to use the code? i.e. like, where to put it?

Sorry to be such a dim-wit but I'm fairly new to this game....

 

[GorD]

Okay, I don't think the first way will work. What about this. With the filtered list, select the top cell in col Bsay b2, enter the formula =A2. Copy cell B2, select the rest of colb down to the bottom of your data, do the Alt; bit paste.

Then if you dont want formulas do edit, paste special, values.

A bit long winded maybe

 

[lichfields]

GorD

Long-winded? It's nothing short of genius! Worked like a charm - and so simple! (Why didn't I think of it?)

Thanks in abundance!!

 

[Jaafar Tribak]

Hi,

Here is another way:

Assign this code to Button on your worksheet :

Assumptions: List in Cloumn A is Named CopyRange and that in column B PasteRange


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Copy_Filtered_Cells()
    <SPAN style="color:#00007F">Dim</SPAN> SourceRange <SPAN style="color:#00007F">As</SPAN> Range, TargetRange <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> Cell <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">If</SPAN> ActiveSheet.FilterMode = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> SourceRange = Range("CopyRange")
        <SPAN style="color:#00007F">Set</SPAN> TargetRange = Range("PasteRange")
        <SPAN style="color:#00007F">With</SPAN> Application
            .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> SourceRange.SpecialCells(xlCellTypeVisible)
                Cell.Copy TargetRange.Cells(Cell.Row)
            <SPAN style="color:#00007F">Next</SPAN>
            .CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Hope this helps.

 

[Norie]

Will this not copy any blank cells as well.

 

[B Caple]

I know this is an old thread, but I have found these steps to work in Excel 2007:

I have a format and formula in a cell that I want to copy only into visible cells, in this case outlined subtotals.

Collapse the outline (or filter your data) to the level you want visible.
Copy the desired cell.
Activate Select Visible Cells tool (I have this on my Quick Access Toolbar).
Select the target range.
Paste.

Please test, but this works for me.

 

[lserge]

I was trying to apply GorD solution posted on May 14th 2004, yet it doesn't work for me
I expected it to work since it did for lichfields, so I thought I was missing on smth. I suspect it is the "bit paste" part from: "Copy cell B2, select the rest of colb down to the bottom of your data, do the Alt; bit paste."

I get the Alt+;, which is a shortcut for selecting only visible cells, still what's the deal with bit paste?

could anyone elaborate on this, please ? I've got a 2007 MsExcel btw, in case this is relevant.