About Us

Trevor Williams

Thanks Tom - it works a treat.

"Tom Ogilvy" wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Adv. Filter - Want to copy to inactive sheet	Maki	Excel Discussion (Misc queries)	7	January 20th 10 12:59 PM
XL2002 - VLOOKUP with variable Sheet Name	Trevor Williams	Excel Worksheet Functions	3	July 29th 08 05:33 PM
XL2002 - OFFSET function and LARGE function	Trevor Williams	Excel Worksheet Functions	3	March 3rd 08 01:40 PM
Calling an inactive sheet...	RJH	Excel Programming	1	July 17th 06 04:23 AM
Using min and max function XL2002	Extremely Aggravated	Excel Worksheet Functions	1	September 23rd 05 06:29 PM

Menu

About Us