Consulting

Results 1 to 17 of 17

Thread: Help to build a data collecti searching from many Workbooks

  1. #1
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location

    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
    Attached Images Attached Images

  2. #2
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    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
    Attached Files Attached Files

  4. #4
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    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

  5. #5
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    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

  6. #6
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    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
    Attached Files Attached Files

  7. #7
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    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

  8. #8
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    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
    Attached Files Attached Files

  9. #9
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    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

  10. #10
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    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
    Attached Files Attached Files

  11. #11
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    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

  12. #12
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    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

  13. #13
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    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

  14. #14
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    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

  15. #15
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    OK Rob, thanks

  16. #16
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    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

  17. #17
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •