1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

combining time recorded for multiple tabs

Discussion in 'VBA Macros' started by david gabra, Jul 15, 2017.

  1. david gabra

    david gabra Member

    I have a problem due to the size of my file
    I have more that 1m rows on different tabs.
    I want to copy all to one tab - not possible and I do not know access.
    I need the 'magical' skills of your vba scripts I believe. (I am not sure if formulae in excel is enough)
    Let me explain.
    I have 15 tabs with the time that was recorded by
    different employees during a 2 year period.
    I want to know by employee the total that they recorded per month form Oct-15 to June 16.

    So I need a new tab that will have the employee names and columns for each month and total that they recorded to work, vacation, and holidays. - I need this to be automated since I have so many tabs and thousands of rows. - excel can't can't cope if I combine to one tab- and neither can I :-(.

    I really need your help, otherwise I am totally stuck.

    I have over 15 tabs - but for the example I have provided I have put 4 tabs to make it simple.

    I need to be able to use your solution for 15 tabs and 5,000 employees.

    I have attached excel, with the expected results for your automation.

    I hope I explained clearly enough.

    I have added the an example of how the new sheet should look.

    Thank you in advance to who ever decides to take on this challenge.

    Attached Files:

  2. david gabra

    david gabra Member

    I am sorry I forgot to add that I need the summary by month to, and this is only for the hours field. I have added a updated file.
    thank you so much.

    Attached Files:

  3. dan_l

    dan_l Active Member

    If it's a one off thing, I highly recommend doing this with powerquery.
  4. david gabra

    david gabra Member

    I need to do this many time.
    I have no idea about power query.
    I need to be able to add other months, since I have only a small sample of months for the example.
    Please can you send me an example in excel.
    thank you for the time and effort.
  5. AlanSidman

    AlanSidman Active Member

    This is a fairly simple task with MS Access. Import all your tabs to one table in Access. Then run a summary Query to get the totals. To get a total by month, you will need to add a field to the table for each record indicating the date. I've provided a sample db to show you in the attached. To do this in Access took about five minutes and it was all manual.

    Alternatively, If you are using Excel 2016, then use the Get and Transform Tab on the Data Ribbon. You can run queries to do exactly what you need to do.


    Attached Files:

    Last edited: Jul 16, 2017
  6. vletm

    vletm Well-Known Member

    david gabra
    Something like this ...

    Go to 'Summary'-sheet
    press [Do Summary]-button.

    Ps. 'Summary'-sheet should be 'Summary' not 'Summary.'!

    Attached Files:

  7. AlanSidman

    AlanSidman Active Member

    When you ran your code, How long did it take to complete. I cancelled out after 6 minutes. With the amount of data the OP has indicated, I wonder how long this code will take to run. Interesting code, but with Access and no code, the information was available in seconds. Just my 2 cents and no disrespect for your code intended.

  8. vletm

    vletm Well-Known Member

    AlanSidman ... hmm ... I just checked and ...
    it took ... oh! ... totally ... ONE SECOND !

    As Chandoo.org - become awesome in Excel.

    If I would try to do this with Access,
    it would take few days and a lot of money!

    Of course, there could be some 'sensitive' points ... like
    1) if Your PivotTable do not have word 'Grand Total'.
    2) Your Excel-version is not in that list or
    if You're using 2016version and
    its 'xlPivotTableVersion16' isn't as I have marked in my code!
    > Did You check those points?

    Would You check again,
    how long time it really would take with Access?
    From ... this moment ...
    including importing tables and so on #5 Reply ...
    until You would get same kind of results as OP wanted.
    Really in seconds?
    Clock is ticking...
    Chirag R Raval and sathishsusa like this.
  9. vletm

    vletm Well-Known Member

    david gabra, AlanSidman
    I modified that code,
    if use Excel2016 (?)-version then it uses the highest xlPivotTableversion and
    there is now a check that, it moves max as many row data as original data!
    No matter which language version You use ('Grand Total'-check).

    ... and still it takes 1 sec or less.

    Attached Files:

    AlanSidman and sathishsusa like this.
  10. AlanSidman

    AlanSidman Active Member

    Just ran your file and it is very fast. Did not bother to go back and run diagnostics as you suggested. Very nice and efficient.

    BTW. It did take less than five minutes for me to import the four files and then build a query and run it in Access. Not saying it is a better solution, only that it was an alternative solution. I do recognize that Access has a steep learning curve and is not available to all. We all have our preferred ways of developing solutions to our problems. :)
    Chirag R Raval likes this.
  11. vletm

    vletm Well-Known Member

    AlanSidman - it's good to know, that it works with You too.
    Maybe that 'xlPivotTableVersion' was incorrect; depends Your Excel-version
    or You didn't pass that 'Grand Total'; language?
    Less than 5 minutes, okay, with Access!
    'My Access' isn't ready ... because ... no Access!
    That's why it takes days ... and that's why Excel!
    Things can do many ways :)
    Chirag R Raval and sathishsusa like this.
  12. david gabra

    david gabra Member

    It seems to work great. I am trying it now with thousands of files

Share This Page