+ Reply to Thread
Results 1 to 13 of 13

VBA: Copy First Sheet of a List of Workbooks to New Workbook

  1. #1
    Registered User
    Join Date
    01-09-2012
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2007
    Posts
    29

    Question VBA: Copy First Sheet of a List of Workbooks to New Workbook

    Greetings,

    I am trying to copy the first worksheet in a list of workbooks to a new workbook using this:
    Please Login or Register  to view this content.
    I've assigned the macro 'mcrAWCF_Copy_Sheet()' to a button, when I hit the button, I first got the very explanatory '400' error, then I inserted some error code to get a description of the error, and got an
    elaborate message in a 'Microsoft Excel" message box that stated: "Excel cannot insert the sheet into the destination workbook because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste command to insert it into the sheets of another workbook." Now, I can do all the cutting and pasting manually, but I was trying to make it easy because there are 27 files to process. I know it gets to the "Get_File_Names" Function because I put a message box in there to see if it made it. Does anyone see anything that leaps out at them as the obvious error? I have been looking at this code for too long I think and cannot for the life of me find what is wrong. It looks like it should work but it doesn't. Any and/or all assistance would be greatly appreciated.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    Are any of your files in excel 2003?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    If you just want to copy the first sheet from all the files in a folder into a new workbook this should do the trick:

    Please Login or Register  to view this content.
    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  4. #4
    Registered User
    Join Date
    01-09-2012
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    Quote Originally Posted by arlu1201 View Post
    Are any of your files in excel 2003?
    No, they are all in excel 2007 format.

  5. #5
    Registered User
    Join Date
    01-09-2012
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    Quote Originally Posted by Domski View Post
    If you just want to copy the first sheet from all the files in a folder into a new workbook this should do the trick:

    Please Login or Register  to view this content.
    Dom
    Thanks, I tried this and got Run-Time error '1004', with the same detailed error message......not sure what to do now...

  6. #6
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    No idea. Code runs fine for me. You must have something else causing the error.

    Can you upload your workbook?

    Dom

  7. #7
    Registered User
    Join Date
    01-09-2012
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    Just to clarify my issue, I am trying to copy the first sheet from each workbook, with multiple workbooks all in a single folder, to a new workbook, with each sheet on a separate worksheet in the new workbook.

    I don't know if this helps but I cannot find any way to make this work, and I would hate to have to do it manually, because I have to process two separate folders, each with 27 separate workbooks...all with multiple worksheets....but I only need the first worksheet in each workbook.

    I would like to thank those who have attempted to assist me so far....and those who might help me in the future.....

    Dom...I took your code and modified it slightly
    Please Login or Register  to view this content.
    When I attempt to execute this the line that generates the error is the 'wbCopyBook.Sheets(1).Copy After:=wbNewBook.Sheets(1)' line.
    Last edited by DHartwig35805; 02-24-2012 at 11:38 AM. Reason: added additional comments

  8. #8
    Registered User
    Join Date
    01-09-2012
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    Quote Originally Posted by Domski View Post
    No idea. Code runs fine for me. You must have something else causing the error.

    Can you upload your workbook?

    Dom

    Sure, how do I do that? lol...Sorry, a newb to this board....

  9. #9
    Registered User
    Join Date
    01-09-2012
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    Ok,

    I have attached a sample copy of one of the workbooks, they all start with the same page, and it is named "Org. Req. Roll-Up", and this is the worksheet I am trying to retrieve from each workbook in the folder ( there are 27 different workbooks).

    I hope this will help and thank you all who are helping me, I really do appreciate this.

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

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    I'm going to guess the error is being caused by you trying to copy a sheet into the workbook and the sheetname already matches a sheet in the new workbook? You need to rename your sheets to something unique before you try and Copy them.

    Here's some ideas along that line:
    Please Login or Register  to view this content.
    _________________
    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!)

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

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    JB, I have marked it solved for them since this thread was opened in February.

  13. #13
    Registered User
    Join Date
    06-06-2012
    Location
    Switzerland
    MS-Off Ver
    MS Excel 365
    Posts
    68

    Re: VBA: Copy First Sheet of a List of Workbooks to New Workbook

    Hi!

    Thanks for this thread, the code is working fine!

    Is there any chance to copy all sheets of all files, instead only the first sheet?

+ 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