CODEWITHSUNDEEP
vbaexcelpaste
Hags
Hags

Reputation: 27

How can I copy a column with formulas and paste those values in the same exact column using VBA?

Sheets("MoM").Select
Range("H2:H").Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
Application.CutCopyMode = False

All I want to do is remove the VLOOKUP Formula by pasting the values within the same column starting at "H2" and ending at the last cell with a formula in it.

Upvotes: 0

Views: 79

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

Is what you have not working? If you want to remove the formula, but keep the values, a super quick way is to just set the range equal to itself:

Dim lastRow&
With Sheets("MoM")
     lastRow = .Cells(.Rows.Count,8).End(xlUp).Row
    .Range("H2:H" & lastRow).Value = .Range("H2:H" & lastRow).Value
End With

Upvotes: 1

David
David

Reputation: 1240

With Worksheets("MoM").Range("H2", Worksheets("MoM").Range("H65536").End(xlUp))
    .Value = .Value
End With

Upvotes: 1

Related Questions