the short answer is
Selection.PasteSpecial Paste:=xlPasteValues

but you also need to get rid of the selects. Example
Sheets("Live History").Select
Sheets("Live History").Range("A1").End(xlDown).Select
Range(ActiveCell, ActiveCell.Offset(0, 20)).Select
Selection.Copy
can be
Sheets("Live History").Range("A1").End(xlDown).Offset(0, 20)).Copy
and then
Sheets("othersheet").Range("A1").End(xlDown).Offse t(0, 20)). _
PasteSpecial Paste:=xlPasteValues

A bit of work with these ideas should help you learn

"ste mac" wrote in message
om...
Hi there, l am struggling with the final tweaks of the macro below, l can
to find and select the correct data(thanks to this ng) after the data
is selected it is copied, then l want to paste the values (values only)
into
the correct ws...l have got it to go to the correct ws but I just don't
know how to get it to paste the values there...have l structured the
macro incorrectly.....

any help would be very welcome....TIA


Sub aaarrrggghhhh()

Dim SheetName As String
Dim sheetnumber As Long
Dim rightsheet As Long
Dim idnumber As String
Dim activeid As String
Dim xlrow As Long
Dim scennumber As Variant

'this bit finally selects and copies the data (took me ages!)
Sheets("Live History").Select
Sheets("Live History").Range("A1").End(xlDown).Select
Range(ActiveCell, ActiveCell.Offset(0, 20)).Select
Selection.Copy

Sheets("Live History").Select
Sheets("Live History").Range("N1").End(xlDown).Select
ActiveCell.Offset(0, 5).Select
idnumber = ActiveCell.Value
Sheets("Selection").Select

For sheetnumber = 1 To 5

xlrow = 3

SheetName = "S" & Format(sheetnumber, "##0")
Sheets(SheetName).Select

rightsheet = Sheets("Live History").Range("H1").End(xlDown).Value
scennumber = ActiveSheet.Cells(3, 2).Value

'This bit finds the right place to paste (suprised myself)
If rightsheet = scennumber Then
ActiveSheet.Range("BW3").End(xlDown).Select
ActiveCell.Offset(1, 0).Select

End If

Next

End Sub