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
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
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
>
>
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
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
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks