Combining Worksheets

    I have a project which will have approximately 20 users preparing worksheets which will contain the same format but which will have information stored on separate lines amongst the 20 worksheets. I need to take these 20 worksheets and merge the information that is entered into one Main worksheet formatted in the same manner.


    First question: Can I create a macro to open these 20 individual spreadsheets (if they even have to be opened to copy).


    Second: How would I write a formula to pull the information from each filled cell and merge it onto the matching cell on the main spreadsheet.


    For example:


    The Main Spreadsheet will have 10 columns and 1000 lines. Columns A through E will be prefilled with policy information.


    Individual users will open a copy of this Main Spreadsheet, fill in Columns F through J for the policies that affect them, and then saving the Main Spreadsheet with a unique name for them (ie; User 1, User 2, etc).


    At the end of the day I need to take the 20 documents from various users and combine their information onto the Main Spreadsheet.


    I'd appreciate any help anyone can provide for a quick way to handle this process.
    Thanks,
    Terry

    Re: Combining Worksheets


    Hi T.


    Given up on the multi user front ??


    Yes, you can open 20 spreadsheets by one of two means.


    If the spreadsheets are seperate workbooks in the same directory, you can use the DIR function to locate the work books and then use the workbooks.open method to open the work book as a work book object. (Sorry - I'm not on my own laptop at the moment otherwise I would provide you with examples).


    You can then apply all the methods or access all the properties of that work book júst as though it is the active work book by using the object name that you assigned the work book to.


    By placing this inside a loop and calling the dir function with no arguments at the end of the the loop, you can then iterate around each work book in the folder, opening and closing each work book in turn.


    Outside this loop, if you open the main workbook as a workbook object, you again have access to that entire workbook along with it's contents.


    By using the find function or even just iterating round the cells of this object you can find the location to insert your data.


    You can help yourself further by typing the following keywords into the VBA editor, placing the cursor on the word and pressing the F1 key:


    Code
    Option explicit   (Makes you declare all variables - helps spot mistakes)
dim    (Declares your variables and what type they are)
set    (Associates an object variable with an object)
Dir     (Provides file names based upon a mask)
while  (Starts a loop)
workbooks.open  (Opens a workbook object)
Range  (Accesses areas of the work book)
workbooks.close  (closes a work book object)
wend   (Ends a loop)


    Regards


    Rich

    Regards


    Rich

    Re: Combining Worksheets


    Thanks Rich. Yes, I gave up on the multi-user function. As several of you advised, that was a receipe for disaster. I'm still ducking the darts that are being thown my way for that attempt.


    I will try the functions you've indicated. I really appreciate the help. It might have been clearer if I'd encluded some sample copies of the spreadsheets. If I can't get this to work, I'll try that.
    Thanks again. I really appreciate the help.
    Terry

    Re: Combining Worksheets


    Hi Terry,


    If no one has posted you some examples when I get back to the hotel tonight, I'll digs some out of my other laptop for you.


    Regards


    Rich

    Regards


    Rich

    Re: Combining Worksheets


    Rich,
    Just wanted to thank you again. I tried your suggestion and it works great. That's exactly what I needed. Thank you once again so much.
    Terry

Participate now!

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

Register Yourself Login