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