you only show copying one range, so here is a guess at what you want:

Sub SortPipeline()

Dim myProbability
myProbability = Array("W7", "W9", _
"W11", "W13", "W15", "W17", "W19", _
"W21","W23")

Set MyFRI = Worksheets("FRI")

For i = 0 To 8

If MyFRI.Range(myProbability(i)) = "H" Then


MyFRI.Range("B7:S8").Offset(i*2,0).Copy _
Sheets("Summary").Range("B7:S8").Offset(i*2,0)


ElseIf MyFRI.Range(myProbability(i)) = "M" Then
MyFRI.Range("B7:S8").Offset(i*2,0).Copy _
Sheets("Summary").Range("B7:S8").Offset(i*2,0)


ElseIf MyFRI.Range(myProbability(i)) = "Pipe" Then
MyFRI.Range("B7:S8").Offset(i*2,0).Copy _
Sheets("Summary").Range("B30:S31").Offset(i*2,0)


End If

Next i

End Sub

--
Regards,
Tom Ogilvy


"Trevor Williams" wrote:

Woah! - now thats code!

Thanks Tom - Is this easily incorporated into the code that I've posted to
Duncan?

Thanks

Trevor

"Tom Ogilvy" wrote:

Unfortuantly Duncan has provided some incorrect information. Offset only
wants to select a cell if you put select after it.

Your posting lacks detail to provide a turnkey answer, but basically
something similar would be:

Private Sub Worksheet_Activate()
Dim rng1 As Range, rng2 As Range, res As Variant
Dim cell As Range, rng1a As Range
With Worksheets("Sheet1")
Set rng1 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

With Worksheets("Sheet2")
Set rng2 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

For Each cell In rng2
res = Application.Match(cell.Value, rng1, 0)
If Not IsError(res) Then
Set rng1a = rng1(res)
rng1a.Offset(0, 1).Resize(1, 18).Copy cell.Offset(0, 2)
End If
Next

End Sub

this worked with sheet1 not only inactive, but hidden as well. I also
placed a version of it in a general module and ran it with a third sheet
active and both sheet1 and sheet2 hidden. Again, it worked perfectly.

--
Regards,
Tom Ogilvy



"Trevor Williams" wrote:

Is it possible to use the Offset function on an inactive sheet?

I am running code when sheet 2 is activated that checks the value of cells
in column W on sheet 1 - if the value is a match I need to copy the current
row from column B to S on sheet 1 and paste it to sheet 2.

I've been trying to use the offset function but having no success as it
seems to always want an active cell, or to select a cell.

Any help gratefully received

Trevor