+ Reply to Thread
Results 1 to 19 of 19

Running But Didn't Copy - Combining Data Multiple Workbooks to a Master Workbook

  1. #1
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Unhappy Running But Didn't Copy - Combining Data Multiple Workbooks to a Master Workbook

    Hi Everyone,

    I have a form that will disperse to a group of people for reports.
    When they return it to me, I have to combine it all to a Master workbook for final report.

    I'm having problems doing so. Also, the problem is for every reporting excel sheet, there will be a particular model name for it, how do I incorporate it into the Master Workbook.

    This is how my reporting excel sheet will look like to give to people.

    I saw this coding in one of the threads, not sure if this is approritate to use in my situation,
    Please Login or Register  to view this content.

    BUT, there's an error in the coding on the "Next ws" line.

    HELP PLEASE
    Attached Files Attached Files
    Last edited by merreena; 05-11-2010 at 09:19 PM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    Code tags added this time
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    There's no code in the example nor any further explanation of what you want to do

  4. #4
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    Hi, thank you! Sorry I missed that rule ): Won't happen again.

    The excel sheet pasted in the first post is the sheet that will be distributed out for people to fill in. I have to collect them back (there will be about 10 of that report coming back) and consolidate it into a single Master workbook.

    I'm not sure how to start or where to paste the code at. Which workbook file do I type in the VBA coding?

    Thank you for your help.

  5. #5
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    Hi, I tried the code by Jerry Beaucaire.


    The code work fine as in it did copied the data from all the reports and consolidate to a main one. But I only wanted to copy the tables (not header), after I used the code, in the Master book, it created seperate header.. look at excel book or print screen for explaination.

    Also, if you look at the excel sheet, at the top of the page, the person doing the report is allowed to choose the vessel name. How should I incorporate it into the master folder?
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by merreena; 05-06-2010 at 10:38 PM.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    This section of code controls what the starting row of the copy is:
    Please Login or Register  to view this content.
    Change A2 to A5, based on what I see in that sample sheet.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  7. #7
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    Hi Jerry!

    Thank you for responding. I tried that and this was what happened.
    In the Masterbook (attached) the header is gone after running.
    Attached Files Attached Files

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    Well, again, you can decide what gets erased in the "clear old data" section of code. Go ahead and setup your report with the titles and all in the first 4 rows the way you want, then change this, same as we did for the copy command:
    Please Login or Register  to view this content.

    You could even delete the whole MsgBox and just always clear the old data with just this instead:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    Hi Jerry, thanks again for your respond.
    Let me try it again.
    I'm sorry. I'm really bad at this =\ my boss is asking me to do this when he knows I know nothing about macros =(

  10. #10
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    Hi Jerry,

    Perfect, it works! Thank you.

    Now that I've got that sorted out. How do I detect the Vessel name (in the For People to fill in file) and copy that to the Master Report file?

    *attached both excel sheet for reference and print screen to explain the motion*
    Attached Images Attached Images
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    anyone can help? =\ i'm still stuck.

  12. #12
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Combining Data from Multiple Workbooks to a Master Workbook

    Hi,

    The code is working fine so now I just want to put the file in a shared drive at work so the only thing I had to change was the path name. I did that. When I clicked run, it looks fine no errors. But, it copied nothing.

    Please Login or Register  to view this content.
    ^That was the change I made. Is there any reason why it's not copying anything in the folder I specify?

  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Running But Didn't Copy - Combining Data Multiple Workbooks to a Master Workbook

    You have "MONTHLY COMPLIATION" in your path - shoudl that be "MONTHLY COMPILATION"?
    Remember what the dormouse said
    Feed your head

  14. #14
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Running But Didn't Copy - Combining Data Multiple Workbooks to a Master Workbook

    Hi thank you for helping me.
    I changed the folder name and I copy it right from the address bar ):
    Still running but not copying anything. The excel sheets in the reports folder are all there. Still didn't work.

    Please Login or Register  to view this content.

  15. #15
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Running But Didn't Copy - Combining Data Multiple Workbooks to a Master Workbook

    The code as posted won't work if the files are on a different drive from the current directory as there is no ChDrive statement. Change the 'Path and Filename' section to this:
    Please Login or Register  to view this content.
    and also make sure there is a backslash at the end of the fPathDone variable (you don't have one currently).

  16. #16
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Running But Didn't Copy - Combining Data Multiple Workbooks to a Master Workbook

    Hi,

    Yup the files were previous in C Drive but now have been transferred to I Drive which is why I changed the Path name

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Running But Didn't Copy - Combining Data Multiple Workbooks to a Master Workbook

    Hi I added, like u suggested, this part

    Please Login or Register  to view this content.
    at the end of the code.

    But error said couldn't find file. But the file name is correct and the directory is right too.

    Attached are printscreen of error msg and folder.
    Attached Images Attached Images

  18. #18
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Running But Didn't Copy - Combining Data Multiple Workbooks to a Master Workbook

    Sorry, I missed a bit - you also need to change the line that opens the workbook from:
    Please Login or Register  to view this content.
    to:
    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    05-06-2010
    Location
    sg
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Running But Didn't Copy - Combining Data Multiple Workbooks to a Master Workbook

    hi romperstomper
    thank you for your help (:
    it works now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1