Multiple lists feeding one named range

  • I have 7 employees who keep similar lists in Excel. Each list has the same four items in it (columns A through D). Column C is the date. There are anywhere from 0 to 100 line items entered into the list for each date.

    I regularly open all seven worksheets, copy the NEW data, and paste it into another worksheet that has a combined list from all 7 people. I use the combined list to feed a pivot table.

    I would like to automate this task... I'm open to suggestions on how to solve this...

    A macro to open each file in turn, copy the new data, paste it at the bottom of the list is the only thing I've been able to think of. I'm not sure the best way to do it as I would want to specify for the macro to ONLY grab the data with a specific date in each of the 7 "child" worksheets to copy them into the "parent" worksheet.

    If there is another way to accomplish this without using a macro, that's good too. Any and all suggestions are welcome.

    Thanks in advance! :)

  • Hi Jas, would this code work for you?

    This code assumes that your summary workbook is named "Summary.xls" and you are pasting the data in the worksheet named "Sheet1". Just make whatever changes you need.

    Let me know if this meets your requirements.


    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  • That looks pretty slick... My VBA isn't strong enough for me to understand everything that's going on, so I'll spend some time with my nose in a book to figure it out! :wink2:

    I'm particularly confused as to how this macro would know what files to open.

    I also don't know what this line does: On Error GoTo 0

    Other than that, I have at least partial clues...

  • On error go to 0 is a to prevent error messages if there is no data.
    Just a quick look at Barrie's code you will be offered the open workbook dialog to open the file that you wish to copy from. You will then have to indicate what you want to copy

  • Feel free to post any other questions you have on the code. I (or anyone else on this board :biggrin: ) will be more than happy to help you out.


    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  • :biggrin:

    Thanks, don't worry, I WILL be back frequently! :wink2:

    In the mean time, I've also ordered one of the books reccomended here, and can't wait for it to show up...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!