How do you refer to a selection on another sheet 1

[Stoffel24]

Lots of questions today! I have a subroutine that selects a range on sheet1. I then wish to calculate the average of that selection on sheet2 using another subroutine. The original selection on sheet1 is still selected. I know I could create a private variable that will be visible in both subroutines and populate that variable with the address of the selection. Then I could use that variable in the other subroutine. But I would prefer to be able to simply use that selection on sheet1 in the formula on sheet2.
Eg
'Assume that several cells on sheet1 are selected

Worksheets("Sheet2&quot.Cells(4, 2).Formula = "=Average(" & Worksheets(1).Selection.Address & &quot"
'This doesn't work

Is this possible?

 

[Zathras]

Here is one way:

Option Explicit

Sub test()
Dim sActiveSheet As String
Dim sh As Worksheet
Dim rng As Range

  sActiveSheet = ActiveSheet.Name
  Set sh = Worksheets("Sheet1")
  sh.Activate
  Set rng = Selection
  Worksheets("Sheet2").Cells(4, 2).Formula _
      = "=Average(" & sh.Name & "!" & rng.Address & ")"
  Set sh = Nothing
  Set rng = Nothing
  Worksheets(sActiveSheet).Activate
End Sub

But if you already know the range to be selected, you can make the code shorter by using that range instead of having to go back to Sheet1 and setting the rng variable. I.e., you might not have to use the .Selection property.

 

[RobBroekhuis]

Stoffel,
You had it almost right! The only problem is there is only one selection object, which refers to the active sheet. So you can use:

sheets(1).activate
s=selection.address
Worksheets("Sheet2&quot.Cells(4, 2).Formula = "=Average(" & s & &quot"

Rob