• 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...

  • 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

RPMdragon

New Member
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... :(
 
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.
 
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
 
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 :)
 
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).
 
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.
 
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.
 
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...
 
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
 

Attachments

  • FFTEST1.xlsx
    10.9 KB · Views: 26
  • FFTEST2.xlsx
    10.1 KB · Views: 24
  • Master.xlsm
    8.9 KB · Views: 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.
 
Code:
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 -.-
 
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 …
 
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!
 

Attachments

  • master.xlsx
    8 KB · Views: 15
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 …
 
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.
 
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.
 
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.
 

Attachments

  • Master.xlsm
    8.7 KB · Views: 28
  • Master Desired Output.xlsm
    8.9 KB · Views: 31
  • FFTEST1.xlsx
    11.4 KB · Views: 21
  • FFTEST2.xlsx
    10.3 KB · Views: 20

Is possible duplicate references between workbooks / worksheets ?

For example a Shipment# exists in both FFTEST1 and FFTEST2 workbooks.
 
Paste this code to Master workbook and
mod SRC variable according to source directory :​
Code:
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 !
 
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
 
Code:
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?
 

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

If you enter a full path remove ThisWorkbook … from variable !
 
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?
 
Back
Top