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?

 

[Vanda_a]

I had the same problem and this is the solution that I use. It doesn't invovle writing macros, since I am still very very new to that and they tend to go haywire on me.

A Non-Macro Solution:
You can go to your excel options, click on customize, then from the drop down choose all commands, then scroll down until you see Select Visible Cells. Select add to customize wuick access toolbar.

To use that button: first copy the cell that you want, then select the cells that you want to copy into, click the select visible cells button, then paste as normal.

Hope this helps!

Hi this cant help. after pasting, data goes mes up after unfilter

 

[kagearm]

Hi, I would like to find an option without using macros, for pasting an unfiltered range of cells, into a filtered range.

My example: I have cell A1 to A10, where A3+4 & A7+8 is hidden. Now i want to paste my unfiltered range (from another sheet), B1 to B10 on top, but I want my B values to be applied from A1 and only to the visible cells, leaving my hidden cells untouched.

I hope it makes sence.

Thanks!
kagearm

 

[rondeondo]

I did a lot of searching before writing a macro. If you do find a way to do it be sure to let me know.
out of interest why do you want to avoid using a macro?

ron

 

[kagearm]

I did a lot of searching before writing a macro. If you do find a way to do it be sure to let me know.
out of interest why do you want to avoid using a macro?

ron

Thanks for your reply!

I will be sure to post here, if I find a solution

I want the "simple" solution, since i'm teaching it to some of my colleagues, who are beginners in excel and are using multiple workbooks. I want them to be able to do it on their own, and not have to worry about a macro, if you catch my drift?

 

[rondeondo]

I know where you're coming from.
At work I have created a lot of macros for myself and some are used by others. I put the macros into a workbook on the network. This workbook can be an xlsm or xlsa file. This way the macros can be run on any other file. As a bonus you can make a ribbon button for them to run a macro from this shared resource. It's a bit manual to set up but easy to use as any built in excel function for the user.

 

[István Hirsch]

Data are in Sheet1 A1:A10, data to insert are in Sheet2 B1:B10

In Sheet 1:

Filter for visible
Delete visible
Restore the list (only hidden remains), former visible cells are now blank

Enter in the column where you want the result (say B1) and copy down:

=IF(A1="",INDEX(Sheet2!B$1:B$10,COUNTBLANK(A$1:A1)),A1)

Excel Workbook
	A	B
1		a
2		b
3	hidden	hidden
4	hidden	hidden
5		c
6		d
7	hidden	hidden
8	hidden	hidden
9		e
10		f
Sheet

 

[kagearm]

Hi István,

Thanks for your answer.

I would like to be able to apply this to column A, and not have to delete the visible, because in my worksheet, i have data in some of the visible columns.
I just made a simple example, sorry for not being precise about it.

I also have data in some of the rows i want to replace with the new value, and I have a lot of rows, so it would be really time consuming to delete all visible rows

But thanks again, would work if you only have a column, or very few rows

 

[Vanda_a]

I put the macros into a workbook on the network. This workbook can be an xlsm or xlsa file. This way the macros can be run on any other file. As a bonus you can make a ribbon button for them to run a macro from this shared resource. It's a bit manual to set up but easy to use as any built in excel function for the user.

hi Rondeondo. How to do it? May I learn pls?

 

[mish6]

Hi guys,

I don't know if someone found a better solution to this but I also struggled with the problem of pasting data into filtered cells and here is my workaround. It is not as elegant as one might wish but it works in all cases and it does not involve the use of macros. Let me explain what I mean by "all cases". You can have several different scenarios where you need to paste data. One is you paste non-filtered data into non-filtered cells which is straightforward and there is no issue here. Another case can be where you paste non-filtered data into filtered cells. Alternatively, you can be pasting filtered data into filtered cells. The last two cases present problems (the last one would be easy if the filtered source data corresponds to the filtered destination cells but this is a rare case and I will be talking about where you have different filters for the source data and destination cells).

The workaround is such. Suppose that you have a destination table with 10,000 rows. If you need to paste information to a filtered 5,000 of them and the source data is either filtered differently (for example it can be 5000 filtered rows out of 6000) or it is not filtered at all, excel will fill the data only up to row 5000 if the source data is not filtered or up to the last row out of the 6000 in your source table. If you select visible cells only excel will refuse the paste, or if you paste using a formula (for example cell A2 = cell B2 of the source table) the formula will again work only up to row 5000, pasting the wrong values as it would not ignore the rows you filtered out.
What you can do is this:

1. Un-filter your destination table.
2. Insert a column before column A. (this column will be your key column).
3. Assign different numerical values (keys) to the cells in your new column A in an ordered fashion (easiest is to just number them from 1 to 10,000 or whatever your last row is)
4. Filter your data back the way you need it.
5. From the source table copy the visible cells in the column you want (in newer versions of excel you do not even need to specifically select visible cells only, just copy). If your source data is unfiltered ignore this step.
6. Paste you data in a new sheet so that it is now unfiltered – in my example there should now be a column of 5000 unfiltered rows without blanks in-between.
7. Go back to the destination table and copy the visible cells from the filtered key column (column A you inserted). Now you have the key values of the 5000 rows you want to paste data to.
8. Go to the unfiltered source data and paste these key values to the left of the unfiltered source data so now you have two columns of, in this case, 5000 unfiltered rows with no blanks.
9. Go back to your destination table and use vlookup, looking up the key in column A in the unfiltered source data. The formula would look like this: =VLOOKUP(A1 (or whatever your first cell is);'[unfiltered source data file.xlsx]New sheet'!$A$1:$B$5000;2;FALSE). Change the formula as you need and apply it to all visible cells so that you don’t mess up the hidden cells (In excel 2010 and 2013 you can just pull down the formula with your mouse and it will automatically be applied to visible cells only). This will paste the data you want to only the 5000 cells of the 10,000 cell table without changing the hidden 5000 cells. The you can delete column A with the keys you assigned, if you want.

This takes a couple of minutes and makes sense if your table is larger than 50 rows. Otherwise it would be faster to just copy-paste each single cell. For large tables it saves a lot of time, at least for me Hope this helps

 

[kagearm]

Hi guys,

I don't know if someone found a better solution to this but I also struggled with the problem of pasting data into filtered cells and here is my workaround. It is not as elegant as one might wish but it works in all cases and it does not involve the use of macros.

.... (too big to quote :P)

This takes a couple of minutes and makes sense if your table is larger than 50 rows. Otherwise it would be faster to just copy-paste each single cell. For large tables it saves a lot of time, at least for me Hope this helps

This actually works! Thank you very much man -

A funny thing though; I'm using Excel 2013 and as you explain, it should be possible to

(In excel 2010 and 2013 you can just pull down the formula with your mouse and it will automatically be applied to visible cells only

This doesn't work for me (and can't figure out why), so i just select all cells, use "alt + ;" to select visible cells only, and then paste my formula (just a workaround if any of you guys have the same problem as me!)

Thanks again.