asked on

Macro deleting formulas instead of copying values

Macro deleting formulas instead of copying values (Excel 2016)

I have a button on a sheet with the below macro assigned, to copy the entire sheet with values from the parent sheet.
But when macro is run, the formulas from the parent sheets are deleted and left with the values only. The copied sheet have the formulas copied instead.

The macro is as below

Sub CopyWorksheetValues()
    ActiveSheet.Copy
    Cells.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Open in new window

I want the formulas in my parent sheet remained as it is and the newly created sheet should only have the values, not formulas.

Is there a way to fix the issue.

Your expert views much appreciated

Regards

Mali

ASKER CERTIFIED SOLUTION

abbas abdulla

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Matt Mali

ASKER

Thanks Abbas and Neeraj

Abbas, I checked your code and is copying and creating a new sheet. It also leave the formulas in the aren't sheets intact. Great work mate.

Neeraj, I liked your code which will be helpful for me in a different project work. In this case, I do not have a destination sheet and my intention was to create a sheet when the macro is run.

Thanks heaps both of you
Regards

Subodh Tiwari (Neeraj)

You're welcome Matt!
Sorry! I got confused with the description. When you said newly created sheet I assumed it as a newly inserted sheet in the same workbook while you were talking about creating a new workbook with the activesheet's content as values only.
I am glad that Abbas picked your question correctly. :)