Vlookup across multiple sheets in another workbook

  • I am using the below code to look up data in another workbook and collect the data. I have been able to get it to work in book 2.xlsm (code is in this "copydata") for my data entry sheet that is located in test.xlsx.


    What I want to be able to do is also get VBA to pull the integral data from the sample 1, sample 2, etc. in test.xlsx and place in book2 integral values. These sheet names/sample names in test.xlsx will change based on the sample name(will be dynamic), but will be the same names in both workbooks.


    Can someone help guide me to how I can add on to this code and address this? I am new to VBA so I am still learning. My actual documents are much larger and so I will need to tweak the reference cells in the end so please try to explain what some of the things mean so I know what I will be doing.


    Additionally, is there a way I can dynamically reference the external workbook name in cell A1, instead of the way I have it defined currently so I don't have to change the name every time?


    Thanks!


    Files

    • Book2.xlsm

      (19.92 kB, downloaded 79 times, last: )
    • test.xlsx

      (12.58 kB, downloaded 74 times, last: )
  • By

    integral data

    I assume you mean

    integral 1 integral 2 integral 3 integral 4 integral 5

    If this is correct, I don't see any integral data in the test workbook so there is no data to pull. Please clarify and attach revised files if necessary.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Is the integral data in these columns?

    weight of empty sample cup (g) sample + sample cup (g) remaining in cup after extraction of sample (g)

    If so, please explain in detail what data you want to pull and where you want to paste it referring to specific cells, rows, columns and sheets

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Mumps


    The integral data/value is coming from the additional sheet tabs in test.xlsx (sample 1, sample 2, sample 3). Cell H10 has the value for integral 1, cell D10 has the name for integral 1.


    The sample names will not always be the same, I just put in example names. Thus I need some way of referencing which tab it will look at. i.e. look at book2.xlsm'data entry'A2 to determine the sample name to get the integral values from (would I use indirect here?), and then look on that sheet name in test.xlsx to find the integral value (vlookup of the integral name on that sheet?).

  • Would this be the result that you want in Book2?

    weight (g) extracted weight (g) integral 1 integral 2 integral 3 integral 4 integral 5
    Sample 1 2 1 2204 1820 20 448 28
    Sample 2 1 0.2 2204 1820 20 448 28
    Sample 3 2 1
    Sample 4 #N/A #N/A

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • In the sample sheets, is it necessary to have blank rows between the integral values? It would be easier if there were no blank row separating those values.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • This macro assumes that you have deleted the blanks row in the test file.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • That works great. Would you happen to know how I can make the file name it pulls from dynamic as well?

    For instance I type the name of it into cell A1 of book 2 and use that for the external workbook name?


    I was able to create an open workbook code that kind of works for that, it seems to error that it can't find it but still opens it. But I'm unsure how to integrate it into this existing code so it will use it.


    Here is the open code which was in book2 that I posed:

  • This macro will prompt you to select the desired file to open.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try deleting that line of code.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I can't reproduce the error. The macro is working properly for me. Can you attach a copy of the the file that is generating the error?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • The macro runs properly for me. What version of Excel are you using?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • VBA for the Mac is different from VBA for Windows. Try the macro on a Windows PC and it should work.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.