Copy and Paste Cell Value Only - Not Formula - from one sheet to another sheet

[arnalba]

I am only newly introduced to VBA code and macros, and am urgently looking for a solution. I would be really really grateful for suggestions in finding a solution to the code below!

The code copies a value from multiples sheets (same range/cell range) and pastes it into the current sheet (ResultsTable)

The cells being copied contains a formula.

Is there a way to copy a cell's numeric value only and not the formula? Or maybe paste the value only (like the Paste Special - Values Only menu item)


Sub cpypaste()
Dim ws As Worksheet
x = 0
For Each ws In Worksheets
If ws.Name <> "ResultsTable"

ws.Range("e108").Copy Destination:=Sheets("ResultsTable").Range("d6").Offset(x, 0)
ws.Range("g111").Copy Destination:=Sheets("ResultsTable").Range("e6").Offset(x, 0)
ws.Range("g109").Copy Destination:=Sheets("ResultsTable").Range("f6").Offset(x, 0)


x = x + 0
End If
Next ws
End Sub

 

[sous2817]

Hello and welcome to the board!

Try use the macro recorder to get the format for pasting values. If you still have problems, post back...

 

[Andrew Poulsom]

Try eg:

Sheets("ResultsTable").Range("d6").Offset(x, 0).Value = ws.Range("e108").Value

 

[arnalba]

Super! Thanks so much, it works.

Try eg:

Sheets("ResultsTable").Range("d6").Offset(x, 0).Value = ws.Range("e108").Value

 

[arnalba]

Thanks for the tip too!

Hello and welcome to the board!

Try use the macro recorder to get the format for pasting values. If you still have problems, post back...