go for it... maybe you could elaborate further on error handling and
appropriate messages, but here's the thing...
Sub GaNaar()
Dim cCel
Dim cNewCel
Dim cNewFile
Dim cNewSheet
Dim cSheet
Dim nPos
cCel = ActiveCell.Value
'cell e.g. b2
On Error Resume Next
Range(cCel).Select
cNewCel = ActiveCell.Value
If cNewCel = cCel Then 'failed move
cSheet = ActiveSheet.Name
'check for other workbook i.e. presence of [aaa]
nPos = InStr(1, cCel, "]")
If nPos > 0 Then 'reference to e.g. 'g:\paul\[other.xls]first tab'!
A13
cNewFile = Replace(Left(cCel, (nPos - 1)), "[", "")
cNewSheet = Mid(cCel, (nPos + 1), 999)
nPos = InStr(1, cNewSheet, "!")
cNewCel = Mid(cNewSheet, (nPos + 1), 99)
cNewSheet = Replace(Left(cNewSheet, (nPos - 1)), "'", "")
On Error Resume Next
If IsObject(Workbooks.Item(cNewFile)) Then
Workbooks(cNewFile).Activate
On Error Resume Next
Workbooks.Open cNewFile
On Error Resume Next
Worksheets(cNewSheet).Activate
On Error Resume Next
Range(cNewCel).Select
Else
nPos = InStr(1, cCel, "!")
cNewSheet = Replace(Left(cCel, (nPos - 1)), "'", "")
cNewCel = Mid(cCel, (nPos + 1), 99)
'cell in other worksheet e.g. other!b2
On Error Resume Next
Worksheets(cNewSheet).Activate
On Error Resume Next
Range(cNewCel).Select
cNewCel = ActiveCell.Value
cNewSheet = ActiveSheet.Name
End If
End If
End Sub
enjoy...
Paul Willekens
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe from this group, send email to
excel-macros+unsubscribegooglegroups.com or reply to this email with the words
"REMOVE ME" as the subject.
