Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Offset function on inactive sheet...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Offset function on inactive sheet...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Offset function on inactive sheet...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Offset function on inactive sheet...
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Offset function on inactive sheet...
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Offset function on inactive sheet...
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Offset function on inactive sheet...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adv. Filter - Want to copy to inactive sheet | Excel Discussion (Misc queries) | |||
XL2002 - VLOOKUP with variable Sheet Name | Excel Worksheet Functions | |||
XL2002 - OFFSET function and LARGE function | Excel Worksheet Functions | |||
Calling an inactive sheet... | Excel Programming | |||
Using min and max function XL2002 | Excel Worksheet Functions |