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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.