Help to build a data collecti searching from many Workbooks

[RIC63]

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

[Rob342]

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

[RIC63]

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

[Rob342]

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

[RIC63]

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

[Rob342]

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

[RIC63]

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

[Rob342]

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

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