Restricted
Board Regular
- Joined
- Aug 9, 2014
- Messages
- 76
Hi,
I have the following 2 workbooks:
https://imgur.com/a/kwU4bvJ
The aim is to be able to click on the command button, which everytime when clicked (a check is ran that checks whether there is a cell within workbook 'Book1_Adventure' with the value of "Not yet activated", and if so, obtains the values from both 'foo1' and 'foo2' columns of the same row, and dumps these two values into the cells G5 and H5 within workbook 'Book2_Adventure'.
I would presume a mixture of VLOOKUP, offset, and perhaps Range.Copy and PasteSpecial, or is there a more efficient way to do this?
So far, my current solution is to ask the user to select the range which they want copied, but this is very inefficient and I thought a button that automatically searches and dumps the values would be much more efficient:
Any suggestions? Thank you for your help
I have the following 2 workbooks:
https://imgur.com/a/kwU4bvJ
The aim is to be able to click on the command button, which everytime when clicked (a check is ran that checks whether there is a cell within workbook 'Book1_Adventure' with the value of "Not yet activated", and if so, obtains the values from both 'foo1' and 'foo2' columns of the same row, and dumps these two values into the cells G5 and H5 within workbook 'Book2_Adventure'.
I would presume a mixture of VLOOKUP, offset, and perhaps Range.Copy and PasteSpecial, or is there a more efficient way to do this?
So far, my current solution is to ask the user to select the range which they want copied, but this is very inefficient and I thought a button that automatically searches and dumps the values would be much more efficient:
Code:
Sub NextValue()
Dim rng As Range
Dim wsMain As Worksheet
Dim wsData As Worksheet
Dim rngToCopy As Range
Application.DisplayAlerts = False
Set wsMain = ThisWorkbook.ActiveSheet
Set wsData = Workbooks("Book1_Adventure.xlsx").Sheets("SRC")
Set rng = Application.InputBox("Select a range", "Obtain Range", Type:=8) ' ask user to select the range
Set rngToCopy = wsData.Range(rng.Address) ' set the range
rngToCopy.Copy ' copy the range
wsMain.Range("G4,G5").PasteSpecial xlPasteValues ' paste into the destination workbook's cells G4 and G5, respectively
End SubAny suggestions? Thank you for your help
Last edited:
