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()
    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


Matt MaliAsked:
Who is Participating?
abbas abdullaCommented:
Try this code

Sub ActiveSheetAsValues()

 With ActiveSheet.UsedRange
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
        End With
 Application.CutCopyMode = False

End Sub

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Your code is copying and pasting back to the same sheet, that's why you are loosing the formulas from that sheet.
It's always better to declare the Sheet variables to hold the source and the destination sheet like below...
Please modify it as per your need...
Sub CopyWorksheetValues()
    Dim sws As Worksheet, dws As Worksheet
    Application.ScreenUpdating = False
    'Set the source sheet
    Set sws = ActiveSheet
    'Add a new sheet and set it as the destination sheet
    Set dws = Sheets.Add(after:=sws)
    'Copy the source sheet
    'Paste to the destination sheet
    dws.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Open in new window

Matt MaliAuthor Commented:
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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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. :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.