Set Row as Variable...
 

I am trying to refer back to any row that was selected prior to starting my
macro. Specifically I am trying to move to A_ of the pre-selected Row, and
follow a hyperlink, gather data from another workbook, and then return to the
original Row in the original workbook so I can paste the new data.

I have been successful in writing code that works from a preselected Row,
but can't seem to figure how to allow any starting point. (Simplified current
code follows)

(New to VBA. Please include the basics. Thanks in advance.)

'// 1) Save Variables
Dim LogBk As String
LogBk = ActiveWorkbook.Name

'// 2) Follow Hyperlink
Range("A33").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("REPORTING").Select

'// 3) Copy Data
Range("12B:12AA").Select
Selection.Copy
Windows(LogBk).Activate
Range("33B:33AA").Select
ActiveSheet.Paste
End Sub

Set Row as Variable...
 

hi.
you could use a range variable.
i notice that you were starting in column A but pasting in column B on the
return.
dim rng as range
Set rng = activecell.offset(0,1)

then after you copy the data
Windows(LogBk).Activate
Rng.Select
ActiveSheet.Paste

regards
FSt1
"Chris T-M" wrote:

I am trying to refer back to any row that was selected prior to starting my
macro. Specifically I am trying to move to A_ of the pre-selected Row, and
follow a hyperlink, gather data from another workbook, and then return to the
original Row in the original workbook so I can paste the new data.

I have been successful in writing code that works from a preselected Row,
but can't seem to figure how to allow any starting point. (Simplified current
code follows)

(New to VBA. Please include the basics. Thanks in advance.)

'// 1) Save Variables
Dim LogBk As String
LogBk = ActiveWorkbook.Name

'// 2) Follow Hyperlink
Range("A33").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("REPORTING").Select

'// 3) Copy Data
Range("12B:12AA").Select
Selection.Copy
Windows(LogBk).Activate
Range("33B:33AA").Select
ActiveSheet.Paste
End Sub

Set Row as Variable...
 

Thanks for the start. 2 things:
1) this method doesn't get me to cell A to follow the hyperlink, but I
think* it will bring me back to the correct place. (*see 2)

2) I wrote:

Dim rng As Range
Set rng = ActiveCell.offest(0, 1)

The VB editor adds a space before the 1, and then errors on the space. What
am I doing wrong?


"FSt1" wrote:

hi.
you could use a range variable.
i notice that you were starting in column A but pasting in column B on the
return.
dim rng as range
Set rng = activecell.offset(0,1)

then after you copy the data
Windows(LogBk).Activate
Rng.Select
ActiveSheet.Paste

regards
FSt1
"Chris T-M" wrote:

I am trying to refer back to any row that was selected prior to starting my
macro. Specifically I am trying to move to A_ of the pre-selected Row, and
follow a hyperlink, gather data from another workbook, and then return to the
original Row in the original workbook so I can paste the new data.

I have been successful in writing code that works from a preselected Row,
but can't seem to figure how to allow any starting point. (Simplified current
code follows)

(New to VBA. Please include the basics. Thanks in advance.)

'// 1) Save Variables
Dim LogBk As String
LogBk = ActiveWorkbook.Name

'// 2) Follow Hyperlink
Range("A33").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("REPORTING").Select

'// 3) Copy Data
Range("12B:12AA").Select
Selection.Copy
Windows(LogBk).Activate
Range("33B:33AA").Select
ActiveSheet.Paste
End Sub