bring data from multiple closed files in folder in multiple sheets

  • hello

    I search macro bring data from multiple closed workbooks in multiple sheets so I have file called search in cell b2 write the number based on col a in multiple closed workbooks into multiple sheets then show the result in file search col a the name file is existed data and col b,c,d as existed in files and the column e sheet name are existed in files

    Files

    • computer.xlsx

      (12.18 kB, downloaded 17 times, last: )
    • eng.xlsx

      (12.18 kB, downloaded 19 times, last: )
    • math.xlsx

      (12.19 kB, downloaded 19 times, last: )
    • search.xlsx

      (10.35 kB, downloaded 16 times, last: )
  • alhagag

    Changed the title of the thread from “bring data from multiple files in folder in multiple sheets” to “bring data from multiple closed files in folder in multiple sheets”.
  • Are all four files already open or does the macro have to open any files?

    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 this macro in the Search workbook. Change the folder path where indicated to the full path where the source files are saved.

    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 tested the macro using the files you posted and it worked properly as you requested. Are you using the macro with the files you posted or with different files? If with different files, please post the files that are not working for you.

    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.

  • Have a look at the attached file. As you can see, all the values are correct. The reason that you have 2 entries for math.xlsx is because the value "1905" in A2 was found on two different sheets, "2011" and "2013". If this is not what you want, please explain in more detail using examples from your files and post copies of the actual files you are using.

    Files

    • search.xlsm

      (20.4 kB, downloaded 25 times, last: )

    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.

  • but i don't bring data repeatedly just update if change old data or add a new data when run macro repeatedly

    Please explain in detail using a few examples from your data and post revised versions of the 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.

  • Try:

    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.

  • Format A5:E26 with "Automatic" font color and centered.

    Files

    • search.xlsm

      (20.4 kB, downloaded 31 times, last: )

    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 line of code:

    Code
    1. desWS.Range("A6:E" & LastRow).ClearContents

    deletes all the old data before copying the current data so there should be no repeated data. Please post copies of your actual files, not pictures, so I can test the macro properly.

    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'm afraid that I will not be able to offer any more help until you post copies of your actual files.

    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.