Copy Filtered Cells with formulas & Paste them back as values without clear filter

[AAP]

Hi,
I would like to create an add-in which I can use to do operation like Copy Visible (filtered) Cells with formulas and paste them back without clearing filter. Any suggestion how can I achieve this.
Thanks

Regards/Amit

 

[Marc L]

Hi !

The VBA Copy method takes only visible cells so no trap,
beginner level : just read VBA help of this method !
Another must see method : PasteSpecial …

Also you can activate Macro recorder and you will get your code …
​

 

[AAP]

Hi !

The VBA Copy method takes only visible cells so no trap,
beginner level : just read VBA help of this method !
Another must see method : PasteSpecial …

Also you can activate Macro recorder and you will get your code …
​

Hi Marc,
Thanks for you suggestion, but I can't see that you copy the filtered range that contains formulas and paste over it again as values.

 

[Marc L]

It's clearly stated in VBA help of PasteSpecial method !

Start to do it manually, Copy then PasteSpecial via right click menu …
Activate Macro recorder to obtain a base of code.

See also in VBA help SpecialCells method …

Another way : Range.Formula = Range.Value …
​

 

[NARAYANK991]

Hi Amit ,

See the attached file.

Apply a filter on the formula field which is column P. Run the macro named CopyFilter. The formulae in the filtered range should be replaced by their values.

Narayan

 

[AAP]

Hi Amit ,

See the attached file.

Apply a filter on the formula field which is column P. Run the macro named CopyFilter. The formulae in the filtered range should be replaced by their values.

Narayan

Awesome, thanks a lot You are really genius.

 

[AAP]

It's clearly stated in VBA help of PasteSpecial method !

Start to do it manually, Copy then PasteSpecial via right click menu …
Activate Macro recorder to obtain a base of code.

See also in VBA help SpecialCells method …

Another way : Range.Formula = Range.Value …
​

Hi Marc,
I appreciate your initiative for helping but sometimes a little example help newbie's to understand the concept. Thanks a lot!
Regards/Amit

 

[Marc L]

With Sheet1.Range("A1:F9")
    .Formula = .Value
End With

Do you like it ? So thanks to click on bottom right Like !
​