Set statement for non active worksheet
 

Is it possible to (when a different sheet is active)
This is extremely simplified, but represents the problem.
I think I'm hoping "With" carries a little more juice.

Sub WorkingIt()
Dim iWish As Range
With Sheet2.Range("rngOne") ' Named range =Sheet2!$D$7:$G$14
.Range(Cells(3, 3), Cells(4, 4)).Value = 45
Set iWish = .Range(Cells(2, 3), Cells(4, 4))
End With
End Sub
--
Jim at Eagle

Set statement for non active worksheet
 

Hi
Nothing wrong with this - Sheet2 need not be active for this to work.
Looking at your code, you might want

Set iWish = .Range(.Cells(2, 3), .Cells(4, 4))

Note the extra periods. This will give you a subrange of "rngOne".
Without the periods it refers to Cells(2,3) to Cells(4,4) on the
worksheet relative to A1.
regards
Paul

On Apr 29, 2:01*pm, Jim at Eagle
wrote:
Is it possible to (when a different sheet is active)
This is extremely simplified, but represents the problem.
I think I'm hoping "With" carries a little more juice.

Sub WorkingIt()
Dim iWish As Range
With Sheet2.Range("rngOne") ' * Named range =Sheet2!$D$7:$G$14
* * .Range(Cells(3, 3), Cells(4, 4)).Value = 45
* * Set iWish = .Range(Cells(2, 3), Cells(4, 4))
End With
End Sub
--
Jim at Eagle

Set statement for non active worksheet
 

Thanks for your help. I had to make the sheet active.
when adding the extra periods a error would come up because I'm working With
a range within the sheet.
I posted a new problem just now if you want a real challenge.
--
Jim at Eagle


" wrote:

Hi
Nothing wrong with this - Sheet2 need not be active for this to work.
Looking at your code, you might want

Set iWish = .Range(.Cells(2, 3), .Cells(4, 4))

Note the extra periods. This will give you a subrange of "rngOne".
Without the periods it refers to Cells(2,3) to Cells(4,4) on the
worksheet relative to A1.
regards
Paul

On Apr 29, 2:01 pm, Jim at Eagle
wrote:
Is it possible to (when a different sheet is active)
This is extremely simplified, but represents the problem.
I think I'm hoping "With" carries a little more juice.

Sub WorkingIt()
Dim iWish As Range
With Sheet2.Range("rngOne") ' Named range =Sheet2!$D$7:$G$14
.Range(Cells(3, 3), Cells(4, 4)).Value = 45
Set iWish = .Range(Cells(2, 3), Cells(4, 4))
End With
End Sub
--
Jim at Eagle