Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

  1. #1
    Board Regular
    Join Date
    Aug 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by Restricted; May 20th, 2019 at 06:43 AM.

  2. #2
    Board Regular
    Join Date
    Aug 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    bump

  3. #3
    Board Regular
    Join Date
    Aug 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    bump - anyone?

  4. #4
    Board Regular
    Join Date
    Aug 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    bump - any help please?

  5. #5
    Board Regular
    Join Date
    Aug 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    bump

  6. #6
    Board Regular
    Join Date
    Aug 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    bump

  7. #7
    Board Regular
    Join Date
    Aug 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    bump

  8. #8
    Board Regular
    Join Date
    Aug 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    bump, anyone?

  9. #9
    Board Regular
    Join Date
    Aug 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    bump...

  10. #10
    Board Regular
    Join Date
    Aug 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    bump

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •