Search code examples
excelvba

Need to paste values, not the formula

I have a generated table created with a Macro, the 1st cell is referencing a dynamic cell that changes with each created table. However, when the table is created I want the value that is carried into the cell to be pasted as a value and stay as is. I don't want it to change as the reference cell changes with the next created table. Current code is below, I tried it 2 different ways and the first column continues to change as the reference cell changes. Any help on how to alter this code so that the value copies in and stays would be great!

   objTable1.DataBodyRange.Cells(1, 1).Value = _
        "=CB5"
Solution

  • Adjusted the solution from comment:

    With objTable1.ListColumns(1).DataBodyRange
    .Value = objTable1.Parent.Range("CB5").Resize(.Rows.Count).Value
End With