Hi Tim:

Try this, although I assume that you want it pasted in the w/sheet1 in cells
F:R.
Also note that it will paste it in reverse order: If you want it in the same
order change for for loop to work backwards as in For lRow = 500 To 1 step -1:

Option Explicit

Sub copy4tim()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lRow As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

For lRow = 1 To 500
If ws1.Cells(lRow, "R") = 1 Then
ws2.Rows(1).Insert
' this is unclear may need to
' modify as you sayu paste into F1 only.
ws1.Range(Cells(lRow, "F"), _
Cells(lRow, "R")).Copy ws2.Range("F1:R1")
ws2.Range("G2:L2").Copy ws2.Range("G1:L1")
End If
Next lRow

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Tim" wrote:

Hi All,

On Sheet1 is a range F1:R500. In some of the cells in column R is number
€œ1€�. What I need is to create a macro which will do the next: for every row
in range F1:R500 if the cell in column R is 1, copy cells F:R, then go to
Sheet2, insert a row, paste copied VALUES from Sheet1 in cell F1 of Sheet2
and copy the formulas from G2:L2 to G1:L1 of the same worksheet.
Any Help is highly appreciated.

Tim