bring data from multiple closed files in folder in multiple sheets

  • Using the macro I posted in Post #12, the attached file shows the results. If this is not what you want, then please explain is detail how the macro is not working for you. Also post a copy of the "search" file that shows what your expected results (entered manually) should look like based on the other 3 files that you posted. This way I can compare the results from the macro to your expected results and see what the difference is.

    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.

  • This line of code:

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

    please tell me where exactly I put this line I think this line will solve what I'm looking for so if is not I will issue whole expected result

    thanks again

  • The macro in the file I posted already contains that line of code and so does the macro in Post #12.

    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.

  • You are not using the correct version of the macro. As I mentioned a few times before, use the version below which I posted in Post #12.

    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.

  • it shows data from before the topic headers

    What do you mean by this?

    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 really don't understand the problem. The file I attached to Post #21 is working properly and is basically the same as the last one you attached. Both files work correctly. If you are using the macro on a different file, then post a copy of that file otherwise I don't think that I can help any further.

    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 really appreciate for your assistance ,time and patience this post takes much more time actually what i attached in post# 28 this what I work for them there is still request , but I can't ask for you now if I don't fix this problem ,it doesn't make sense my request


    I'll try to find where is problem , if I found out I'll inform you


    best regards,


    alhagag

  • hi, Mump I was trusting your code works finally works as what I say you don't believe me the problem is sheet in post 28 I use file search i insert a new sheet "result" to understand what's my problem surprisingly the I apply the code in sheet result and works astoundingly and i delete the sheet search and rename sheet result to search also works so well do you believe that but I ask myself how it is work for you without any problem you know this is the first time faces problem like this this problem caused headache

    any way i have some requests if they're possible

    1- I see this code is slow and I tested it gives me this value 0.680 is it normal if there is better I would appreciate

    2- I would understand some parts of code about this line especially numbers


    Code
    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 5).Value = Array(srcWB.Name, fnd.Offset(, 1), fnd.Offset(, 2), fnd.Offset(, 3), ws.Name)

    3 - 3 - I want when i search specific data it copies to bottom data has ever already searched IN in other words every time i search a new data then copies to under old data have ever already search i hope this point is clear

  • It looks like the macro is running in under one second. That's pretty fast. I don't think that I can make it faster because it has to search multiple files and multiple sheets within each file.


    That line of code finds the first available blank cell in column A and resizes it to 5 columns (A:E). Then it places the workbook name in column A, the value 1 column to the right of the found value (fnd.Offset(, 1)) in column B, the value 2 columns to the right of the found value (fnd.Offset(, 2)) in column C, the value 3 columns to the right of the found value (fnd.Offset(, 3)) in column D and the sheet name in column E.


    If you want to keep all the previous data, delete this line of code:

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

    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 the attached file. It uses cell AA1 as a helper cell. It also now contains a Worksheet_SelectionChange macro in the search sheet code module.

    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.

  • You are very welcome. :)

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!