Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Pivot Table - Object variable or with block variable not set? | Excel Programming |