Copy/Paste values with a filter on

[jlax34]

Is there a simple way to copy/paste values when you have a filter on? I often have formulas in spreadsheets I want to work with and want to slowly get rid of them as I'm working through a column. I will filter on the results on want and want a way to remove the formulas just from the visible cells at that time (without having to download an add in as that isn't an option at work).

 

[mumps]

Does your sheet have headers in row 1? A simple macro could do this for you unless by "add in" you mean that you don't want a macro.

 

[jlax34]

I was referring to the add ins that you can download like Text Tools and others. Just not an option at my company or it may solve the issue. As far as a macro working, I'm not sure I was looking for go that far and was curious if there happened to be something easy that I have somehow overlooked after all these years.

The sheet I have allocates a quantity based on a subtotal that changes with the filter. Most sheets where I run into this, I can just break the link (because its often a Vlookup), or remove the filter and copy/paste values on the whole column. In this case, removing the filter changes the value of the cell and isn't an option. Not sure its worth the effort to write a macro though...

 

[mumps]

A macro could simply copy only the visible data and paste only the values back to the same range thus deleting the formulas in that range. Let me know if you're interested in trying this approach and which ranges, rows or columns you want to copy/paste the values.