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

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:

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 Sub


Any suggestions? Thank you for your help
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top