CODEWITHSUNDEEP
excelvba
Devon
Devon

Reputation: 1

Copy & Paste Values in Same Location

I am new to macros and am having trouble creating a macro that will copy a range of cells and then paste just the values back into the same cells. Basically I just need to get rid of the formulas and paste the value back into the same location.

I have tried the coding below, but I am getting an error that says "Run-time error '1004': PasteSpecial method of Range class failed." Can anyone help?

Sub CopyPaste()
Worksheets("Sheet1").Range("A1:G105").Copy
Worksheets("Sheet1").Range("A1:G105").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Upvotes: 0

Views: 972

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57703

Just do

With Worksheets("Sheet1").Range("A1:G105")
    .Value = .Value
End With

This will replace formulas with their values and is much faster than copy and special paste.

Upvotes: 1

Related Questions