Copy-pasting as value in a filtered table of a range

[erikavb04]

Hello,

I have a question concerning copy-pasting as value in a filtered table of an active selection.

Situation description:
I have a table and in every cell is a formula. If I add a filter, and select a specific range of the filtered data, I want to copy-paste that data from formulas to values.
Now I do it line by line by pressing ctrl+alt+v, and select value.

Can you help me writing a macro for this?
I tried the following, but I miss skipping the hidden cells, and the active selection/range:

Code: Select all

Sub ValuePaste()
   
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
   
End Sub

Thank you,
Erika

[NoSparks]

Selection.SpecialCells(xlCellTypeVisible).Copy

[erikavb04]

Hello NoSparks,

Thank you for your help.

With your code it will copy the visible, selected cells. But how can I paste these cells as values (in the cells are formulas, but I want to make them values).
I tried this, but it gave me an error:

Code: Select all

Sub ValuePaste()

Selection.SpecialCells(xlCellTypeVisible).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub


[NoSparks]

2 questions

what's the error?

where are you trying to paste it?

Selection won't change between the first two lines in the procedure and I doubt you want it pasted right where it's being copied from, and just incase you do you can use .value=.value