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

**DHartwig35805** · 02-24-2012, 09:54 AM

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.

**arlu1201** · 02-24-2012, 10:18 AM

Are any of your files in excel 2003?

**Domski** · 02-24-2012, 10:21 AM

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

**DHartwig35805** · 02-24-2012, 10:42 AM

Originally Posted by arlu1201

Are any of your files in excel 2003?

No, they are all in excel 2007 format.

**DHartwig35805** · 02-24-2012, 11:23 AM

Originally Posted by Domski

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...

**Domski** · 02-24-2012, 11:33 AM

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

Can you upload your workbook?

Dom

**DHartwig35805** · 02-24-2012, 11:34 AM

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.

**DHartwig35805** · 02-24-2012, 11:39 AM

Originally Posted by Domski

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....

**DHartwig35805** · 02-24-2012, 11:46 AM

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.

**JBeaucaire** · 02-24-2012, 04:37 PM

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.

**JBeaucaire** · 08-21-2012, 11:05 AM

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

**arlu1201** · 08-21-2012, 11:46 AM

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

**FixandFoxi** · 09-12-2012, 06:05 AM

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?

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions