Hi Duncan

Here's what I have so far. The basic idea is that if any of the cells in
the array called myProbability contain 'H' or 'M' then copy the details from
the sheet called FRI to a table in the Summary sheet. If the cell contains
the word Pipe, then copy the details to a different table on the Summary
sheet - I've yet to add the code to find the empty cells in the tables on the
Summary...

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").Copy 'THIS IS WHERE I WAS TRYING THE OFFSET
FUNCTION
Sheets("Summary").Range("B7:S8").Select
ActiveSheet.Paste

ElseIf MyFRI.Range(myProbability(i)) = "M" Then
MyFRI.Range("B7:S8").Copy
Sheets("Summary").Range("B7:S8").Select
ActiveSheet.Paste

ElseIf MyFRI.Range(myProbability(i)) = "Pipe" Then
MyFRI.Range("B7:S8").Copy
Sheets("Summary").Range("B30:S31").Select
ActiveSheet.Paste

End If

Next i

End Sub


"Duncan" wrote:

Trevor,

Offset does want to select the cell, if you post what code you have
then there might be another way of achieving it though.

Duncan


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