Macro - Jump to a cells Reference

[rsulliva]

I would like the following code to create a duplicate window, tile these windows vertically, follow the reference from the originally selected cell, then select this cell's referenced cell in the second window. Everything seems to be working correctly until the last line of code where it will not select the second window and the corresponding cell the original cell is referencing. Any suggestions? Thanks

Sub SplitWindowFollowCellRef()

Dim OriginalActiveCell As Range
Set OriginalActiveCell = ActiveCell

'Create a new duplicate window and tile these two windows vertically
ActiveWindow.NewWindow
Windows.Arrange ArrangeStyle:=xlVertical

'Refer to the original active cell and copy its formula reference and go to this cell in the second window
MyRef = OriginalActiveCell.Formula
Application.Goto Reference:=MyRef

End Sub

 

[AlphaFrog]

    'Refer to the original active cell and goto the cell referenced in its formula in the second window
    Application.Goto ActiveSheet.Range(OriginalActiveCell.Precedents.Address)

 

[rsulliva]

I tried this code but it keeps telling me that there is a "run time error 1004 - no cells were found". Also, note that the original selected cell is on Sheet 1 and the referenced cell is on Sheet 2. Same workbook though.

 

[AlphaFrog]

What exactly is in the activecell (the original selected cell) as seen in the formula bar?

 

[rsulliva]

Hey AlphaFrog,

The exact link in the cell in the formula bar is ='Files to be Completed'!B19 but note the following . This macro should be able to run on any cell within any sheet within any workbook and go to the link/reference within that cell. If the formula for whatever reason has more than one link, it should only go to the first one.

Thank you.

 

[AlphaFrog]

This is a bit of a kludge, but it seems to work... for me.

Sub SplitWindowFollowCellRef()

Dim OriginalActiveCell As Range, FirstPrecedent As Range
Set OriginalActiveCell = ActiveCell
ActiveCell.ShowPrecedents
ActiveCell.NavigateArrow True, 1
Set FirstPrecedent = ActiveCell
Application.Goto OriginalActiveCell
OriginalActiveCell.ShowPrecedents Remove:=True

'Create a new duplicate window and tile these two windows vertically
ActiveWindow.NewWindow
Windows.Arrange ArrangeStyle:=xlVertical

'In the 2nd window, goto the 1st precedent of the original activecell's formula
Sheets(FirstPrecedent.Parent.Name).Select
Range(FirstPrecedent.Address).Activate

End Sub

 

[rsulliva]

Hey AlphaFrog,

This code works great!

Thank you!