1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP Another workbook with multiple sheets

Discussion in 'VBA Macros' started by RPMdragon, Jan 9, 2017.

  1. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Hope someone can help me,

    I have spent many hours trying to come up with a vlookup macro function that would search for a chosen value on all the workbooks in a certain folder and give me the output. The issue is that on all those other workbooks they all have many sheets so the function would have to loop through all the sheets in every workbook to find the value. So to summarize:

    take value from all inputs in column A starting from A2, vlookup on all different workbooks (and worksheets inside those workbooks) for this value, give me the desired values in the 5th collumn from those workbooks back in the original worksheet I ran the macro from on Column B.

    Please help as I have spent MANY hours trying to get this done with no success... :(
  2. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Hi Ninjas,

    I understand you all must be really busy but I really need help with this. I guess I just wanted to add 1 more condition also to the above scenario. if the vlookup could spit out the values from the 5th column to the 10th column on the active worksheet on column B through G would be perfect. I'm even willing to donate some if I could help some help on this please.
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,508
    Hi ,

    It would help if you could upload your workbook with as much data as possible in it.

    Also specify the complete folder path ; will this folder contain all types of Excel files such as .xls , .xlsx , .xlsb , .xlsm ,... ?

    Will the lookup range or at least column be the same in all worksheets and workbooks ?


    Narayan
  4. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Hi Narayan,

    Thank you for responding and I will try to answer as much as possible:

    It is hard for me to upload files as it involves many large excel files with confidential data so what I will try to do is to be as descriptive as possible:

    I have 1 folder containing 6 workbooks with multiple sheets, all the sheets and every workbook have the same table headers however with different data obviously.

    I have in another folder the "master" workbook which I need to extract some information from those other workbooks and have it displayed on my master workbook.

    In the master workbook the cell references by which I want to search the other workbooks all can be found in column A.

    then I need the macro to search for the values in column A through all the workbooks > once it finds a match take the cell values from column E through L and paste them on column B through G on the master workbook

    Every single workbook for all cases are .xlsx

    since every month I will be creating a new sheet on the master file I need to be able to change the macro such that by month (aka sheet) I can get the values to be pasted onto that new sheet

    all the tables in all the workbooks are dynamic and change regularly so every time I run the macro I need it to clear all the values that were previously there (within the B through G columns) and re-paste them.

    I hope that this makes sense :)
  5. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,151
    Sanitize your data and upload sample workbook that accurately represent your actual data set for about 25 to 50 rows.

    Personally, I'd suggest using PowerQuery or MS Query to merge all sheets into single table and do lookup operation (either combine it with merge process or do it after all sheets are merged).
  6. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Hi Chihiro,

    I cannot combine the documents as there is a reason for them to be separated into 6 different workbooks. As I mentioned on the previous post, all the workbooks are dynamic and being constantly updated by other parties as well.
  7. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,151
    I'm not suggesting altering your set up. Just use query to return data set you want.

    In any case, without sample of all 6 source workbooks and desired output I can't help you further.
  8. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Hi Chihiro,

    I thought that my explanation was detailed enough for any ninja to be able to make a simulation of what it is that I am asking for... 1 folder with 6 workbooks with more than 1 sheet with data, another folder with my master worksheet. Once I run the macro on the master workbook, it should pick up all the values in the 1st column, look for them on the other 6 workbooks across all sheets, once it finds it give me the values from that other file that I need back into my master tracker. if I update the master sheet, once I rerun the macro it should 1st clear or overwrite the data obtained the 1st time since it might have changed and give me the same updated range again... If you want I can make and attach 7 small scale workbooks for the sake of simulation but that is the idea...
  9. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Yesterday I took some time to design a few workbooks and perhaps that will help you get a better idea of what I am trying to do. Note the 3 attachments (2 that will be searched and the "master" workbook where I need the desired output).

    Please save the 2 FFTEST files in 1 folder: C:\Users\me\Desktop\TEST\FFTEST

    and save the master file in another folder: C:\Users\me\Desktop\TEST\Master

    I need the "need" columns from the other 2 docs to appear on the green section of the master file.

    if I rerun the macro (assuming I changed a value on the "reference" column in the master file) it should clear the previous output and give me the new one.

    Once I move to the next sheet "Feb2017" on the master I should be able to change a value on the macro do display the output on that sheet instead..

    The explanation was already mentioned on previous posts so I hope this is sufficient for you guys to help me out (don't know how else to explain :( )

    If something is still not clear please feel free to ask I will do what I can

    Attached Files:

  10. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Hi Ninjas,

    I have done all that I was asked to do and even attached sample files, I would really appreciate some help here... Sorry to keep following up on this but I REALLY need this.
  11. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Code (vb):
    Sub VlookMultipleWorkbooks()

        Dim lookFor As Range
        Dim srchRange As Range
        Dim r1 As Range
        Dim r2 As Range
        Dim i As Range

        Dim book1 As Workbook
        Dim book2 As Workbook
        Dim book3 As Workbook

        Dim book2Name As String
        book2Name = "FFTEST1.xlsx"    'modify to actual files later
     
        Dim book3Name As String
        book3Name = "FFTEST2.xlsx"

        Dim book2NamePath As String
        book2NamePath = ThisWorkbook.Path & "\" & book2Name
       
        Dim book3NamePath As String
        book3NamePath = ThisWorkbook.Path & "\" & book3Name

        Set book1 = ThisWorkbook

        If IsOpen(book2Name) = False Then Workbooks.Open ("C:\Users\me\Desktop\TEST\FFTEST\FFTEST1.xlsx")
        If IsOpen(book3Name) = False Then Workbooks.Open ("C:\Users\me\Desktop\TEST\FFTEST\FFTEST2.xlsx")
        Set book2 = Workbooks("FFTEST1.xlsx")
        Set book3 = Workbooks("FFTEST2.xlsx")

        Range("L2:Q65536").Clear
       
     
        Set lookFor = book1.Sheets(1).Range("A2:A10")
        Set r1 = book2.Sheets(1).Range("A:K")
        Set r2 = book3.Sheets(2).Range("A:K")
         
        For Each i In lookFor
        If IsNumeric(Application.Match(i, r1, 0)) Then
       
        lookFor.Offset(0, 11).Value = Application.VLookup(lookFor, r1, 2, False)
        lookFor.Offset(0, 12).Value = Application.VLookup(lookFor, r1, 3, False)
        lookFor.Offset(0, 13).Value = Application.VLookup(lookFor, r1, 4, False)
       
        Else
     
        lookFor.Offset(0, 11).Value = Application.VLookup(lookFor, r2, 2, False)
        lookFor.Offset(0, 12).Value = Application.VLookup(lookFor, r2, 3, False)
        lookFor.Offset(0, 13).Value = Application.VLookup(lookFor, r2, 4, False)
       
       
        End If
        Next i
       
        book2.Close
        book3.Close
     
    End Sub

    Function IsOpen(strWkbNm As String) As Boolean

        On Error Resume Next

        Dim wBook As Workbook
        Set wBook = Workbooks(strWkbNm)

        If wBook Is Nothing Then    'Not open
           IsOpen = False
            Set wBook = Nothing
            On Error GoTo 0
        Else
            IsOpen = True
            Set wBook = Nothing
            On Error GoTo 0
        End If

    End Function

    Hi guys,

    I honestly don't understand much about Macros and tried to use a base reference I found online to do what I am asking it to do based on the files I attached earlier. I figured that if I can get it to spit out the value I want for 2 cases I can just keep increasing this macro until I fill in for every sheet however my code is not doing what I intended it to do.

    Basically, I wanted it to match the value on the master sheet to the range r1 and if there was a match then spit out the values I want and if there was not a match look at range r2 (eventually I would increase this to cover every range across all sheets) and finally if I can't find it in any of the files just display blank.

    Please help -.-
  12. Marc L

    Marc L Excel Ninja

    Messages:
    2,971
    Hi !

    It is often the problem with a code « found online »
    which was not created specially for your need !

    I very do not understand this code 'cause using directly VLOOKUP
    as an Excel formula does not need to open source workbook ‼
    Even by code …

    The other way for those who do not want to follow an easy formula
    is the Range.Find VBA method as explained in VBA inner help
    and well documented by its sample …
  13. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Hi Marc,

    I appreciate the quick response and as you can clearly tell I have no training/courses/previous knowledge with macros and have been trying to find online closely related things to what I need since I clearly don't know what I am doing... but at least im trying!

    I saw the Range.Find section you were referring to and it seems like it would work however, would it be able to search for a range of values through all the workbooks in a folder with many sheets and give me the values of the 4 columns adjacent to it on the same line?

    for example:

    take the values I put in the master doc i just attached

    search it on all the workbooks and worksheets inside the folder path:"C:\Users\me\Desktop\TEST\FFTEST"

    if it finds the value in one of the sheets of any of those workbooks copy all values in columns D:G

    paste them on the master doc i attached where it says "Give me the output here" section

    I am sorry i am being so persistent but I would really appreciate it if you could show me how that is done. Please help!

    Attached Files:

  14. Marc L

    Marc L Excel Ninja

    Messages:
    2,971
    Whatever by formula or by Find method,
    it is value by value and worksheet by worksheet …

    At least start your code for a value searched within one worksheet
    using easy Find method and its sample.
    See also in VBA inner help Offset and Range.Copy method …
    You can also use Macro Recorder and operate manually
    a search and a copy : you will have your own base code !

    If it works you can post your code here to mod it
    for several values as multi worksheets …

    It seems from post to post your need slight changes !
    As we can't always mod easily code
    (like « oups I forgot to tell you … » and waste our time)
    so at least with a last crystal clear and complete explanation
    with source workbooks and desired results according
    from these source workbooks someone may bring some help …

    I have not to much time to spend on decrypting
    so I yet wait for news from NARAYANK & Chihiro …
  15. RPMdragon

    RPMdragon New Member

    Messages:
    26
    ok Marc,

    Thanks for your time and since its very late here tomorrow morning when I arrive at the office I will try to make one last crystal clear and complete explanation attaching all the files including one with the desired output.
  16. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,151
    Like I said. MS Query or PowerQuery will work best here.

    For PowerQuery, no need for code.

    Alternately, if you really must use VBA... you can try ADODB.
  17. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Hi Chihiro,

    Thank you for the advice and I see how all the options you suggested would work however, since this is for a company I cannot purchase any add ons, also the workbooks inside the folder where the source data is located are being worked on by external parties therefore they might from time to time remove workbooks, add workbooks, rename workbooks therefore I don't see how any direct linkage would work.

    As for Marc's request,

    Please find attached complete sample workbooks and I hope here comes the crystal clear explanation.

    Attached you will find 2 different FF files (source data), the master workbook (before I run the macro) and Master desired output (after I run the macro)

    Please save the FFTEST workbooks in folder path: "C:\Users\me\Desktop\TEST\FFTEST"
    and save the Master workbook in folder path: "C:\Users\me\Desktop\TEST"

    Take all the "shipment #" from the master file and search them on all the workbooks and worksheets inside the folder path:"C:\Users\me\Desktop\TEST\FFTEST"

    if it finds the value in one of the sheets of any of those workbooks copy values found in the columns "ETD ETA ATD ATA Value"

    paste the desired values back into the master such that it looks like the desired output folder.

    Every time I run this macro it needs to replace all the old values with the latest run since they might change

    Sorry I cannot share the real files as they have lots of confidential data. I hope these samples give you guys the idea of what I am trying to achieve.

    Attached Files:

  18. Marc L

    Marc L Excel Ninja

    Messages:
    2,971

    Is possible duplicate references between workbooks / worksheets ?

    For example a Shipment# exists in both FFTEST1 and FFTEST2 workbooks.
  19. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Hi Marc,

    no, all the shipments# are unique values and are not repeated in any other sheet/workbook
  20. Marc L

    Marc L Excel Ninja

    Messages:
    2,971
    Paste this code to Master workbook and
    mod SRC variable according to source directory :​
    Code (vb):
    Sub Demo()
              Dim SRC$, VA, R&, F$, oWb As Workbook, oWs As Worksheet
                  SRC = ThisWorkbook.Path & "\Source\"
           If Dir(SRC, vbDirectory) = "" Then Beep: Exit Sub
                   VA = [A1].CurrentRegion.Columns(1).Value
        With CreateObject("Scripting.Dictionary")
                For R = 2 To UBound(VA):  .Item(VA(R, 1)) = R:  Next
                    F = Dir(SRC & "*.xlsx")
           Do Until F = ""
              Set oWb = GetObject(SRC & F)
                For Each oWs In oWb.Worksheets
                       VA = oWs.UsedRange.Columns(1).Value
                    For R = 2 To UBound(VA)
                        If .Exists(VA(R, 1)) Then
                            oWs.Cells(R, 6).Resize(, 5).Copy Cells(.Item(VA(R, 1)), 12)
                           .Remove VA(R, 1)
                            If .Count = 0 Then oWb.Close: Exit Do
                        End If
                    Next
                Next
                  oWb.Close
                    F = Dir
            Loop
                .RemoveAll
        End With
              Set oWb = Nothing:  Set oWs = Nothing
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
    Thomas Kuriakose and RPMdragon like this.
  21. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Hi Marc,

    Sorry took a while to respond. I am getting an error when I run it saying "Run time error 52: Bad file name or number" and the line the debugger highlights is this one:If Dir(SRC, vbDirectory) = "" Then
  22. Marc L

    Marc L Excel Ninja

    Messages:
    2,971


    Mod variable with a right directory path …​
  23. RPMdragon

    RPMdragon New Member

    Messages:
    26
    Code (vb):
    Sub Demo()
              Dim SRC$, VA, R&, F$, oWb As Workbook, oWs As Worksheet
                  SRC = ThisWorkbook.Path & "C:\Users\me\Desktop\TEST\FFTEST\"
            If Dir(SRC, vbDirectory) = "" Then Beep: Exit Sub
                    VA = [A1].CurrentRegion.Columns(1).Value
        With CreateObject("Scripting.Dictionary")
                For R = 2 To UBound(VA):  .Item(VA(R, 1)) = R:  Next
                    F = Dir(SRC & "*.xlsx")
            Do Until F = ""
              Set oWb = GetObject(SRC & F)
                For Each oWs In oWb.Worksheets
                        VA = oWs.UsedRange.Columns(1).Value
                    For R = 2 To UBound(VA)
                        If .Exists(VA(R, 1)) Then
                            oWs.Cells(R, 6).Resize(, 5).Copy Cells(.Item(VA(R, 1)), 12)
                            .Remove VA(R, 1)
                            If .Count = 0 Then oWb.Close: Exit Do
                        End If
                    Next
                Next
                  oWb.Close
                    F = Dir
            Loop
                .RemoveAll
        End With
              Set oWb = Nothing:  Set oWs = Nothing
    End Sub
     
    Hey Marc,

    I pasted the code and changed the source Directory. Is there any other location I need to change?
  24. Marc L

    Marc L Excel Ninja

    Messages:
    2,971

    Check the value of variable and you will understand your path is wrong !

    If you enter a full path remove ThisWorkbook … from variable !
  25. RPMdragon

    RPMdragon New Member

    Messages:
    26
    GREAT! IT WORK BEAUTIFULLY!!!

    Thank you so MUCH!

    One more question, if on the master tracker the Shipment number was in column C but on the source files the reference is still in column A, what would I have to adjust to make it work?

Share This Page