Help with cell referencing

[tonalator15]

Hi

I am new to this forum and am currently having trouble with VBA. I am trying to have my code go to the worksheet that is in the formula of another cell.

Example:

='Sheet1'!$A$1

I would like to be able to go to "Sheet1" and then select data from that sheet using more VBA code. Is there a way to follow the reference in a cell?

Thanks for any help in advance!

 

[SkipVought]

Hi,

Not really sure what you are asking.

You have a cell containing a formula. You want to find the SHEET and CELL?

'select the cell with the formula
Dim a, i As Integer, sTheSheet As String, sTheRange As String
With ActiveCell
   a = Split(.Formula, "!")
   If UBound(a) = 1 Then
      sTheSheet = Right(a(0), Len(a(0)) - 1)
      sTheRange = a(1)
   Else
      sTheSheet = ActiveSheet.Name
      sTheRange = Right(a(0), Len(a(0)) - 1)
   End If
End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Skip, I'd replace this:
[tt]sTheSheet = Right(a(0), Len(a(0)) - 1)[/tt]
with this:
[tt]sTheSheet = Mid(Replace(a(0), "'", ""), 2)[/tt]