Paste to visible cells only

lichfields

New Member
Joined
May 11, 2004
Messages
16
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 :oops:

Can anyone help?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
I don't think this is possible without code.
 
Upvote 0
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....
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0
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.
 
Upvote 0
Will this not copy any blank cells as well.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top