CODEWITHSUNDEEP
vbaexcel
Crebuloboldoaps
Crebuloboldoaps

Reputation: 19

Copy values, not formulas, on a worksheet to another

I added a macro to copy a worksheet to another worksheet, so that any changes made after that point can be compared to the original. However, my macro copies over formulas instead of just the values, so when something changes, both sheets change, and the copy serves no purpose. What I have is:

Worksheets("First Sheet").Cells.Copy _
Destination:=Worksheets("Second Sheet").Cells

Is there an easy way to fix this? Thanks!

Upvotes: 1

Views: 7994

Answers (3)

Vityata
Vityata

Reputation: 43585

After your macro, you can write this one:

With Worksheets("First Sheet")
    Worksheets("Second Sheet").Range(.UsedRange.Address).Cells.Value2 = .UsedRange.Value2
End With

It takes the values of the first sheet and it puts them to the second sheet. The trick with UsedRange is needed, because Worksheets(2).Cells.Value2 = Worksheets(1).Cells.Value2 goes above the usual resources of a normal PC.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

This will skip all formula cells in the first sheet:

Sub KopyKat()
    Dim r As Range, addy As String
    For Each r In Worksheets("First Sheet").Cells.SpecialCells(2)
        addy = r.Address
        r.Copy Destination:=Worksheets("Second Sheet").Range(addy)
    Next r
End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

You need to use Copy >> PasteSpecial and paste only values, this is a 2-line syntax:

Worksheets("First Sheet").Cells.Copy
Worksheets("Second Sheet").Cells.PasteSpecial xlPasteValues

Upvotes: 1

Related Questions