VBA copy values not formulas

[Rapportering]

I have a VBA that copy data from 19 sheets into one sheet(Mastersheet).
Problem is that it copies data incluing formulas. I want it to copy values. How can I change the code?

Code is:

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Mastersheet"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

 

[JonXL]

You can do away with your copy-paste code entirely and just set the cells you want to 'paste' into to be equal to the value of the cells you want to 'copy' from.

For example,

MyWS1.Cells(1, 2) = MyWS2.Cells(2, 2).Value

... 'copies' the value from B2 on the Worksheet object MyWS2 and 'pastes' it to B1 on the MyWS1 Worksheet object.

 

[nikio8]

Record yourself, copy, paste as values. Use that code, rather than copy destination