Pasting values to visible cells only

[Muthukumar_Natesan]

I want to copy values from a particular column in an excel file and paste it in another file in a particular column where I have excluded some rows by means of filtering. However when I paste the values, the values get pasted in the rows that are hidden. I checked extensively in the net to find a solution for it, but I didn't get any direct solution but only work arounds. I understand that there is a third party vendor (Kutools) who has solution for it, but I am looking for a solution from Microsoft.

[#2]

@Muthukumar_Natesan 

See this reply for a macro that will do what you want.

[#3]

Hi Hans,
Thanks for your prompt response. This macro will not solve the issue that I reported. Let me try to explain better:
1) Opened an excel file FIle_A and copied 20 rows of column B from this file. Please note that there is no filter applied in File_A.
2) Opened another excel file File_B and filtered column A that lists only 20 rows. Now I tried to paste the contents that I copied from File_A.

After the completion of the 2nd step, I expect that contents that I copied from File_A should be copied to the visible cells in File_B. However it is not happening. The contents are copies to the cells that are hidden (filtered out) in File B.

[#4]

@Muthukumar_Natesan 

I suspect that you haven't actually tried that macro. I have tested it and it does exactly what you want.

[#5]

Hi Hans,
Thanks a lot. I tried it again today. It worked. I am not sure what I missed when I did last time. I tried with 2 example and it worked. Thanks a ton :-). Is there a way to place a feature request to Microsoft to implement this feature and include it under the existing "Paste Special" menu item?

[#6]

You can assign the macro to a toolbar buttons, so the macro will be available on any open workbook & easy to access.

[#7]

@Muthukumar_Natesan 

From within Excel: select File > Feedback and click Send a Suggestion.

And you could add your vote to Ability to paste to filtered (visible) cells on the Excel Feedback Portal.

[#8]

Thanks Hans. I have upvoted. The count is 72. I hope Microsoft implements this feature very soon. Till then I will use the macro that you shared.

[#9]

Thanks Cangkir. I did assign the macro to the toolbar button. If I have to use this macro in other excel files, I have to ensure that this xlsm file is kept open when I am working on the other excel files and also have to set "enable VBA macros" option which is bit risky if I fail to change it to old settings after completing my task. Hence I would prefer a long term solution which is having this feature included as part of "Paste Special" menu item.

[#10]

@Muthukumar_Natesan 

You might store the macro in a module in your Personal Macro Workbook PERSONAL.XLSB.

It will then be available in all workbooks.