copying sheet one from every workbook in a folder

**Nick_in_Dubai** · 10-05-2009, 06:10 AM

Afternoon all....

ok this is either going to be really simple or over the top difficult so lets give it a go.

In a folder i have a large number of files all formatted the same but with different data in, basically what i want to do is have a mastersheet in each folder that when you run code it will open the first workbook, copy sheet 1 and paste it to sheet 1 in the mastersheet. then open the next workbook again copy sheet 1 find the next empty row in sheet 1 of the mastersheet and paste below, then basically repeat through all the files. There are changing number of files in the folder so it needs to be able to loop and open every workbook. at the moment the sheets are called ME1, ME2 etc etc and the mastersheet called mastersheet.

Is this something we can do?

Nick

**JBeaucaire** · 10-05-2009, 11:23 AM

Try this macro I already had for this task. Be sure to edit the Directory location, marked in red:

Please Login or Register  to view this content.

**Nick_in_Dubai** · 10-06-2009, 02:42 AM

This looks great, i am getting a compile error: Argument not optional at the ChDir stage. I have change the location to the correct one????

**B2009** · 10-06-2009, 12:29 PM

Great concept - this would definitly come in useful. Just a few questions though:

1. Arn't you supposed to define what ChDir is?
2. Where do you specify the worksheet within thhe workbook that you are interested in copying accross?
3. Is it possible to add to the Macro to automatically name the tab of the worksheet that is carried accross - maybe make the name of the tab referenced to a cell reference within the worksheet?

Many thanks

**JBeaucaire** · 10-06-2009, 01:13 PM

Originally Posted by B2009

1. Aren't you supposed to define what ChDir is?

The command given in the code is correct. ChDir is an operating system setting, changes the working directory. Since I listed the string to change to, we have defined it.

If what you mean is "don't you have to edit that string so that it's accurate"? Yes, you do. That's why I highlighted it.

2. Where do you specify the worksheet within the workbook that you are interested in copying across?

Based on your indication that is was Sheet1, I used the "Sheet1.Activate" to make that the active sheet in each workbook.

You can do that many ways:

Please Login or Register  to view this content.

3. Is it possible to add to the Macro to automatically name the tab of the worksheet that is carried across - maybe make the name of the tab referenced to a cell reference within the worksheet?

Everything is possible.

Currently the macro is copying data across and adding it into a single table. There are no inclusions for tab names.

Are you saying your "Sheet1" has a different name each time? If so, then you definitely want to use the line I highlighted in red above for activating the sheet.

If you provide some information about WHERE exactly in the master workbook you would want to see the tab name from the copied workbooks to appear, I can suggest some changes.

Remember, I can't see your sheet so explain completely what you're wanting, or post before/after examples.

**Nick_in_Dubai** · 10-07-2009, 12:51 AM

J,

Sorry reading through this is think its going to work well, i just need some advise on the compile error: Argument not optional at the ChDir stage....

**JBeaucaire** · 10-07-2009, 01:02 AM

Sorry, take out the = sign.

Please Login or Register  to view this content.

**Nick_in_Dubai** · 10-07-2009, 01:35 AM

Almost their, it opens the other document but i get a run time error and the data doesnt copy across. I have attached the two sheets as an example

**JBeaucaire** · 10-07-2009, 02:49 AM

I've corrected one small error in the macro above, this line:

Please Login or Register  to view this content.

**Nick_in_Dubai** · 10-07-2009, 03:08 AM

I really appreciate the quick responses, the file is opening and closing now successfully but the data is not pasting into the master sheet

**JBeaucaire** · 10-07-2009, 08:47 AM

I just ran it myself on your files, made 4 or 5 files out of your one ME1.xls, it's working great. I can't speak to what is different in your local environment.

Please Login or Register  to view this content.

copying sheet one from every workbook in a folder

LinkBack

Thread Tools

Rate This Thread

Display

copying sheet one from every workbook in a folder

Re: copying sheet one from every workbook in a folder

Re: copying sheet one from every workbook in a folder

Re: copying sheet one from every workbook in a folder

Re: copying sheet one from every workbook in a folder

Re: copying sheet one from every workbook in a folder

Re: copying sheet one from every workbook in a folder

Re: copying sheet one from every workbook in a folder

Re: copying sheet one from every workbook in a folder

Re: copying sheet one from every workbook in a folder

Re: copying sheet one from every workbook in a folder

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions