Store cell & workbook references, to retrieve later in Macro
 

I am writing a retrieval program. The first part works great (Thanks RBS),
but I need help getting back to my starting workbook and the Row I want. They
are currently entered directly in my Macro, but I want them to change each
time I run the Macro.

Sub Test ()
'////START CODE////
Dim rng As Range
Dim oHyperLink As Hyperlink
'////SELECT FILE LINK////
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select Project Link", _
Title:="picking a hyperlink", _
Type:=8)

If rng Is Nothing Then
On Error GoTo 0
Exit Sub
Else
Set oHyperLink = rng.Hyperlinks(1)
If oHyperLink Is Nothing Then
MsgBox "Need to pick a cell with a hyperlink!", , "picking a
Hyperlink "
On Error GoTo 0
Exit Sub
End If
End If
On Error GoTo 0
'////FOLLOW LINK//SELECT TAB//SELECT DATA//
oHyperLink.Follow NewWindow:=False, AddHistory:=True

Worksheets("L4_Request").Activate

Range("A10:H10").Select
'////TURN CELL VALUES INTO TEXT//COPY//
Selection.Copy
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Selection.Copy
'////RETURN TO LOG//SELECT CELLS//PASTE//
Windows("Test Book2.xls").Activate
Range("B10").Select
ActiveSheet.Paste
End Sub

Store cell & workbook references, to retrieve later in Macro
 

how do you want them to change?

Regards
FSt1

"Chris T-M" wrote:

I am writing a retrieval program. The first part works great (Thanks RBS),
but I need help getting back to my starting workbook and the Row I want. They
are currently entered directly in my Macro, but I want them to change each
time I run the Macro.

Sub Test ()
'////START CODE////
Dim rng As Range
Dim oHyperLink As Hyperlink
'////SELECT FILE LINK////
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select Project Link", _
Title:="picking a hyperlink", _
Type:=8)

If rng Is Nothing Then
On Error GoTo 0
Exit Sub
Else
Set oHyperLink = rng.Hyperlinks(1)
If oHyperLink Is Nothing Then
MsgBox "Need to pick a cell with a hyperlink!", , "picking a
Hyperlink "
On Error GoTo 0
Exit Sub
End If
End If
On Error GoTo 0
'////FOLLOW LINK//SELECT TAB//SELECT DATA//
oHyperLink.Follow NewWindow:=False, AddHistory:=True

Worksheets("L4_Request").Activate

Range("A10:H10").Select
'////TURN CELL VALUES INTO TEXT//COPY//
Selection.Copy
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Selection.Copy
'////RETURN TO LOG//SELECT CELLS//PASTE//
Windows("Test Book2.xls").Activate
Range("B10").Select
ActiveSheet.Paste
End Sub

Store cell & workbook references, to retrieve later in Macro
 

Basically, I have a work request form that I want to pull data from onto a
master Log. The idea is to pick a row, set a hyperlink to the work request of
choice, use that to get the data, and then paste the data next to the
hyperlink. In other words, I want to select the row of my choosing and keep
refrencing back to it, with the columns always the same. Everything basically
works up until the final paste. It's set to Row 10. It would also be
preferable to be able to use the same macro even if the Log file name is
changed. Right now the file name is also set to a pre-entered name.


"FSt1" wrote:

how do you want them to change?

Regards
FSt1

"Chris T-M" wrote:

I am writing a retrieval program. The first part works great (Thanks RBS),
but I need help getting back to my starting workbook and the Row I want. They
are currently entered directly in my Macro, but I want them to change each
time I run the Macro.

Sub Test ()
'////START CODE////
Dim rng As Range
Dim oHyperLink As Hyperlink
'////SELECT FILE LINK////
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select Project Link", _
Title:="picking a hyperlink", _
Type:=8)

If rng Is Nothing Then
On Error GoTo 0
Exit Sub
Else
Set oHyperLink = rng.Hyperlinks(1)
If oHyperLink Is Nothing Then
MsgBox "Need to pick a cell with a hyperlink!", , "picking a
Hyperlink "
On Error GoTo 0
Exit Sub
End If
End If
On Error GoTo 0
'////FOLLOW LINK//SELECT TAB//SELECT DATA//
oHyperLink.Follow NewWindow:=False, AddHistory:=True

Worksheets("L4_Request").Activate

Range("A10:H10").Select
'////TURN CELL VALUES INTO TEXT//COPY//
Selection.Copy
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Selection.Copy
'////RETURN TO LOG//SELECT CELLS//PASTE//
Windows("Test Book2.xls").Activate
Range("B10").Select
ActiveSheet.Paste
End Sub

Store cell & workbook references, to retrieve later in Macro
 

Did I loose my audience. I'll be more concise:

Need:
- Code to reference the current workbook name during a Macro. (so I can
return to it)

- Code to store and return to the same row or cell I selected at the begining.

Any ideas would be appreciated.


"Chris T-M" wrote:

Basically, I have a work request form that I want to pull data from onto a
master Log. The idea is to pick a row, set a hyperlink to the work request of
choice, use that to get the data, and then paste the data next to the
hyperlink. In other words, I want to select the row of my choosing and keep
refrencing back to it, with the columns always the same. Everything basically
works up until the final paste. It's set to Row 10. It would also be
preferable to be able to use the same macro even if the Log file name is
changed. Right now the file name is also set to a pre-entered name.


"FSt1" wrote:

how do you want them to change?

Regards
FSt1

"Chris T-M" wrote:

I am writing a retrieval program. The first part works great (Thanks RBS),
but I need help getting back to my starting workbook and the Row I want. They
are currently entered directly in my Macro, but I want them to change each
time I run the Macro.

Sub Test ()
'////START CODE////
Dim rng As Range
Dim oHyperLink As Hyperlink
'////SELECT FILE LINK////
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select Project Link", _
Title:="picking a hyperlink", _
Type:=8)

If rng Is Nothing Then
On Error GoTo 0
Exit Sub
Else
Set oHyperLink = rng.Hyperlinks(1)
If oHyperLink Is Nothing Then
MsgBox "Need to pick a cell with a hyperlink!", , "picking a
Hyperlink "
On Error GoTo 0
Exit Sub
End If
End If
On Error GoTo 0
'////FOLLOW LINK//SELECT TAB//SELECT DATA//
oHyperLink.Follow NewWindow:=False, AddHistory:=True

Worksheets("L4_Request").Activate

Range("A10:H10").Select
'////TURN CELL VALUES INTO TEXT//COPY//
Selection.Copy
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Selection.Copy
'////RETURN TO LOG//SELECT CELLS//PASTE//
Windows("Test Book2.xls").Activate
Range("B10").Select
ActiveSheet.Paste
End Sub