Macro to switch to another sheet, grab ref to any cell, paste ref in current sheet??

D

drhansenjr

I am looking for a way (either macro or VBA) to perform one simple task:
while in *any cell* of your choice in the current worksheet, switch to
another specific sheet, choose *any* cell, grab a reference to that
cell (not its value), switch back to the first worksheet and slap in
the reference of the cell in the other sheet.

This would be the automated equivalent of doing the following
manually:

1) Select *any cell* (say *F11*) in the current sheet (Call it
"*Destination*").
2) Press "=" (no "ENTER")
3) Click the tab for the other sheet (call it "*Source*")
4) Select *any cell * (say *D50*) within *Source*
5) Press the ENTER key.
6) *F11* now contains the reference formula: *"=Source!D50"*

Ideas?

I tried to record a macro to do this, but as soon as I type "=" and
select the tab for the second sheet, I can end the macro without
selecting a specific cell in the "source" sheet and pressing ENTER --
which of course grabs the reference of that one cell, but doesn't allow
me to choose a different cell each time.

Help!!
 
D

Dave Peterson

How about pointing and clicking on that other cell:

Option Explicit

Sub testme03()

Dim myCell As Range
Dim myOtherCell As Range

Set myCell = ActiveCell

Set myOtherCell = Nothing
On Error Resume Next
Set myOtherCell = Application.InputBox(prompt:="Select a cell--any cell", _
Type:=8).Cells(1)
On Error GoTo 0

If myOtherCell Is Nothing Then
'user cancelled
'do nothing
Else
If myCell.Parent.Parent.Name = myOtherCell.Parent.Parent.Name _
And myCell.Parent.Name = myOtherCell.Parent.Name Then
myCell.Formula = "=" & myOtherCell.Address _
(rowabsolute:=False, columnabsolute:=False)
Else
myCell.Formula = "=" & myOtherCell.Address _
(rowabsolute:=False, columnabsolute:=False, _
external:=True)
End If

End If

End Sub

If you want to go to a completely different workbook, select it via Window on
the worksheet menubar.
 
D

drhansenjr

This is great --

I just had to tweak it a bit as follows:

Sub testme03()

Dim myCell As Range
Dim myOtherCell As Range

Set myCell = ActiveCell

Set myOtherCell = Nothing
On Error Resume Next
SHEETS(\"SOURCE\").SELEC
Set myOtherCell = Application.InputBox(prompt:="Select a cell--an
cell", _
Type:=8).Cells(1)
SHEETS(\"DESTINATION\").SELEC

On Error GoTo 0

etc...

Now I just need to figure out how to restrict the selectable range i
the source sheet (or cause the macro to say "oops" if the selected cel
is out of range.

I started by defining range names for the cells at the top and botto
of the list of selectable cells --

So I'm trying to do something like (crappy pseudocode):

IF
[destination-row.column] < [selectable_range_start.column]
OR
[destination-row.column] > [selectable_range_start.column]
OR
[destination-row.row] < [selectable_range_start.row]
OR
[destination-row.row] > [selectable_range_end.row]
THEN
msgBox ("WUPS!!")
END IF

Sorry -- I haven't touched VB/VBA in any serious way in several year
-- I am VERY rusty
 
Top