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.
bring data from multiple closed files in folder in multiple sheets
-
alhagag -
November 22, 2020 at 3:36 PM -
Thread is marked as Resolved.
-
-
-
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.
-
I explained what I want I put what I want in sheet result
-
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.
Code
Display MoreSub CopyData() Application.ScreenUpdating = False Dim srcWB As Workbook, desWS As Worksheet, ws As Worksheet, fnd As Range, LastRow As Long Set desWS = ThisWorkbook.Sheets("Search") LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row desWS.Range("A6:E" & LastRow).ClearContents Const strPath As String = "C:\Forum Help\claxa\" 'change folder path to suit your needs ChDir strPath strExtension = Dir(strPath & "*.xlsx") Do While strExtension <> "" Set srcWB = Workbooks.Open(strPath & strExtension) For Each ws In srcWB.Sheets Set fnd = ws.Range("A:A").Find(desWS.Range("A2").Value, LookIn:=xlValues, lookat:=xlWhole) If Not fnd Is Nothing Then With desWS .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) End With End If Next ws srcWB.Close False strExtension = Dir Loop Application.ScreenUpdating = True End Sub
-
-
ok I copy and paste but there is problem it shows data from before the topic headers despite your code indicates from row6
please check my file and see what happened
-
it shows data from before the topic headers
What do you mean by this?
-
would you check what show me in file search
-
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.
-
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:
-
-
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.
-
thanks Mumps for your time and your codes
god bless you
-
-
You are very welcome.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!