Copy values of visible cells

[ArnolddG]

Hi,
I want to copy the values of visible cells in a table.
I am using this:

Please Login or Register  to view this content.

But I get an error 1004 "The command cannot be used on multiple selections"

In the immediate window this results in a valid range with multiple cells:

Please Login or Register  to view this content.

So the range is not the issue.
What am I doing wrong here?

[nathansav]

Please Login or Register  to view this content.

Does this help?

[Hawkeye16]

Personally I would use something like

Please Login or Register  to view this content.

Instead of copy and paste.

[ArnolddG]

Thanks for your reply Nathansav,
It works if I combine it with the solution I got from Hawkeye16 and add instead of .Pastespecial xlValues:

Please Login or Register  to view this content.

So my issue is solved now...
Thanks again.

[ArnolddG]

Thanks for your reply Hawkeye16,

Your solution did not give always resulted in the right values.
In a range of A3:A5,A7 the value of A3 was also pasted in A7, but your solution combined with the solution I got from Nathansav it worked fine.
Thanks again.

[Hawkeye16]

Interesting, wonder why it didn't work. Well, as long as you have a working solution now that is good. Thanks for the feedback!

[AB33]

Please Login or Register  to view this content.

[ArnolddG]

I thought I had it solved, but when doing some more testing it appears that non of the 3 solutions seems to be working when I apply a filter on the table.
So I made a sample file with all of the 3 solutions:

Sample file copy visible values only.xlsb

In the example I first add a formula and that paste the values of that formula.
Can anyone get this working even in a filtered mode ?

Thanks for helping....

[AB33]

Your code will not work with tables(If that is what you have).

[AB33]

Please Login or Register  to view this content.

[ArnolddG]

I am using tables, so if this does not work I guess I'll have to use a for next loop for every row...