J.E. McGimpsey was kind enough to answer my copy & paste problem and
even supplied the macro below.. l decided to go with his example, l
just have one question...l have been trying to insert a 'pastevalues'
only syntax,
as the code copies across all the formatting in the copyrange, whereas
l would very much like to have just the values, could J.E or another
kind person help me out once more please...
l have stuck ".PasteSpecial(xlPasteValues)" all over the place but
just cannot
get it to work....thanks very much for any help...

seeya ste

Public Sub aaarrrggghhhh()
Dim copyRange As Range
Dim sheetNumber As Long
Dim rightSheet As Long
Dim idNumber As String

With Sheets("Live History")
Set copyRange = .Range("A1").End(xlDown).Resize(1, 20)
idNumber = .Range("N1").End(xlDown).Offset(0, 5).Value
rightSheet = .Range("H1").End(xlDown).Value
End With
For sheetNumber = 1 To 5
With Sheets("S" & sheetNumber)
If .Cells(3, 2).Value = rightSheet Then _
copyRange.Copy Destination:= _
.Range("BW3").End(xlDown).Offset(1, 0)
End With
Next sheetNumber
End Sub

JE posted a correction:

Public Sub aaarrrggghhhh()
Dim copyRange As Range
Dim sheetNumber As Long
Dim rightSheet As Long
Dim idNumber As String

With Sheets("Live History")
Set copyRange = .Range("A1").End(xlDown).Resize(1, 20)
idNumber = .Range("N1").End(xlDown).Offset(0, 5).Value
rightSheet = .Range("H1").End(xlDown).Value
End With
For sheetNumber = 1 To 5
With Sheets("S" & sheetNumber)
If .Cells(3, 2).Value = rightSheet Then _
Range("BW3").End(xlDown).Offset( _
1, 0).Resize(1, 20).Value =copyRange.Value
End With
Next sheetNumber
End Sub

--
Regards,
Tom Ogilvy

ste mac wrote in message
om...
J.E. McGimpsey was kind enough to answer my copy & paste problem and
even supplied the macro below.. l decided to go with his example, l
just have one question...l have been trying to insert a 'pastevalues'
only syntax,
as the code copies across all the formatting in the copyrange, whereas
l would very much like to have just the values, could J.E or another
kind person help me out once more please...
l have stuck ".PasteSpecial(xlPasteValues)" all over the place but
just cannot
get it to work....thanks very much for any help...

seeya ste

Public Sub aaarrrggghhhh()
Dim copyRange As Range
Dim sheetNumber As Long
Dim rightSheet As Long
Dim idNumber As String

With Sheets("Live History")
Set copyRange = .Range("A1").End(xlDown).Resize(1, 20)
idNumber = .Range("N1").End(xlDown).Offset(0, 5).Value
rightSheet = .Range("H1").End(xlDown).Value
End With
For sheetNumber = 1 To 5
With Sheets("S" & sheetNumber)
If .Cells(3, 2).Value = rightSheet Then _
copyRange.Copy Destination:= _
.Range("BW3").End(xlDown).Offset(1, 0)
End With
Next sheetNumber
End Sub

Thanks Tom... you see posts a lot sooner than they appear on my screen..!

l had already posted before l seen J.E's modification...

cheers

seeya ste