XL2002 - Offset function on inactive sheet...

[Trevor Williams]

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

 

[Duncan]

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]

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

 

[Tom Ogilvy]

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.

 

[Trevor Williams]

Woah! - now thats code!

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

Thanks

Trevor

 

[Tom Ogilvy]

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

 

[Trevor Williams]

Thanks Tom - it works a treat.

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