Reference/copy data from another workbook based on another cell's value

[Restricted]

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:

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 Sub

Any suggestions? Thank you for your help

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...ok-based-another-cells-value.html#post5280495

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.