Help to build a data collecti searching from many Workbooks

Thanks in advance if someone can give help,
below I try to explain what I need

I have a workbook made of many worksheets ( each one has the same template, see Picture 1).


I would like to use one of the worksheets as an interactive dashboard to build customized report ( see Picture 2 )...I mean that after populating some input fields (F9, G9, F10, F11 and G11..all or JUST one..two..) by clicking a button a macro will read recursively on every worksheets and will write down row after rows the datas found on every column of each row that match (contain ) the setted search criteria. A second button 'reset' will clear the input fields and the entire extracted data collection.


Es. if I put in F10 2019 05 01, in F11 2019 05 31 and in G11 Cambiellus then after the macro run I will find starting from cell F13 (Prog. 1) every data found plus
in N13 the Customer name found in C1 of every template
in O13 the ID ref. found in D1 of every template and
in P13 the Description found in E1 of every template

thanks in any case

RIC63
You need to provide a copy of the workbook
what data you want to extract
where you want it to go
What field is the search criteria?
Rob

Hi Rob
thanks for your interest, below my answers to your requests for clarification

You need to provide a copy of the workbook : Attached
what data you want to extract : all data found on the row plus some info about the source worksheet as in the sample in worksheet 'Report'.
where you want it to go : in to a dedicate worksheet ( in the attached file I have used the 'report' worksheet to explain my request ).
What field is the search criteria? : see what I tried to explain in the worksheet called 'Report'

Thanks again
regards

Hi Ric
Had a quick peak
Why do need 60+sheets when all the data which is the same can be put into 1 database
This make the search facility a lot quicker as you are only searching 1 sheet for the criteria
Searching 60 sheets for data is going to slow the program down and i don't know how long its going to run for
with only 1 database you can then create Pivots, charts etc or even use PoweBI
Plus all the dates need to be dates without spaces
Rob

Thanks Rob
I have to use this tool because I have to share information with people who need to visualize data in a simple and guided way and some worksheets as soon as their life cycle is over I can easily delete them, speed of execution is not important.


Thanks anyway for the support and advice


riccardo

Hi RIC
just to give you an update i have completed the 1st part" What a challenge that was !" have added some test data so i can check it
Have attached a copy of where i am at the moment ok
There is still quite a bit of work to do yet
Let me know what you think so far ok
Rob

good morning Rob
I took a look at the code but I'm not so expert to interpret what you wrote to make a judgment, I can only thank you.
I tried to insert data in the F10 F11 and G11 fields and launch the macro using the button but at the moment it has no effect, is it correct?


I have not seen the data you wrote about having added for testing anyway, if it can be useful it is not a problem to change the date field without spaces.



thanks again rob

Hi Ric
Have made a few changes to the file
At the moment, in the report Tab you need your dates in F3 & F4 & a name in G4
G4 will search by Cust Or people depending on the dates entered
Make sure all macro's are enabled to run
Also i have added on the report a button to clear the data once you have finished with it otherwise it will append the next data to it ok
Do you need anything else to do ?
Rob

Hi Rob
I did not have time to adequately test the routine but it seems to work correctly and I thank you again for this
the only thing I noticed is that it is necessary to enter a complete data because the routine extracts the data.. I mean that it would have been useful to be able to insert even a partial data such as 'Carl' instead of the complete data 'Carl Lenz'


anyway, thanks and as soon as I have the chance to do some more in-depth tests, I'll update you
regards

Hi Ric
Have changed the search to global so you can enter a part name or the full name
It will still need your dates but u can alter these to what you like ok
Rob

Hi ROB
I tried to copy the code and the buttons in the excel file where I have the real data, the routine starts but stops where in the list it finds 'FindAll' displaying the error message 'Sub or Function not defined'


do you have any idea what the problem might be?


thanks for what you can tell me

Hi Ric
The Error you describe relates to the Function in the code module
Did you create a module 1st, the Function FindAll must sit in this module as per example
Did you copy in the Report sheet exactly as it was on the example also when you copy the command buttons over make sure its command buttons 1 & 2
recheck that the code has go over correctly to your programme & don't forget to compile & save the file 1st
Failing that you will have to email your file & i'll check it ok
Rob

Hi Rob the problem is solved, thanks.
I'll ask you another question ... if I remove the space from the dates I entered then switch from YYYY MM DD format to YYYYMMDD the routine you developed at that point can also check the dates?
Thanks again
regards
riccardo

Hi Ric
It won’t make any diff if you remove the spaces it will chk on what you have entered
bearing in mind that the data on the database must also match ie no spaces
Rob

OK Rob, thanks

good morning rob
sorry but I noticed this thing: in some cases the data that is reported in the 'report' worksheet does not have the same starting data format, it is possible to copy exactly the cell that meets the search criteria in the report sheet so that his original data is retained?


thank you
regards
riccardo

Hi Ric
This bit of the code checks whether the Dates fall into what's specified on F3 & F4
Just remember that if there are any extra spaces or less spaces and they do not match, this check it will not extract it, if you can give me an example of the data your putting in then i'll recheck it

Code:

---

If TestDate1 < Worksheets("Report").Range("F3").Value Or TestDate1 > Worksheets("Report").Range("F4").Value Then
            GoTo End2
        End If

---