Copy a row from 3000 excel files stored in a folder and paste it in a new workbook

**hubertus** · 07-27-2009, 07:47 AM

Hi,

Here's my situation:

I have 3000 workbooks in a folder.
Each has a worksheet named "Sheet1"
There are 13 colums and 2 rows of data in Sheet1.

I need to copy the second row from all excel files stored in the folder and
paste them into a blank workbook.

I really do not know how to proceed with this. I have basic VBA knowledge.

Thank you very much for any advice, I will greatly appreciate it.

**Phil_V** · 07-27-2009, 08:31 AM

The following should do as you require. Note that you will need to set the folder to look in appropriately.
This code should be placed into a module in the VBE.

Please Login or Register  to view this content.

**hubertus** · 07-27-2009, 08:49 AM

Thank you!! Works like a charm!

If it's not too much trouble for you, could you please explain the code a little bit? I'd very much like to learn some new VBA stuff.

Thank you again!

**Phil_V** · 07-27-2009, 09:13 AM

Sorry, I would normally comment the code for posters to learn from, but I was rushed when posting this

Here it is with some comments. Any other questions feel free to ask

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**hubertus** · 07-27-2009, 09:34 AM

Awesome! Thank you!

**hubertus** · 07-28-2009, 10:13 AM

Hi Phil!

Thank you again for the code, works great.

However, i came accross a little setback while using it. The code copies cells incl. formulas in them. Is it possible to copy just values?

I know how to copy and paste values between sheets but I am not able to do that between workbooks. I tried to modify your code to this, but I wasnt successfull.

Could you help me with this as well?

Thank you!!

**Phil_V** · 07-28-2009, 10:34 AM

I think the following change should do it for you:

From:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

Hope that helps

Copy a row from 3000 excel files stored in a folder and paste it in a new workbook

LinkBack

Thread Tools

Rate This Thread

Display

Copy a row from 3000 excel files stored in a folder and paste it in a new workbook

Re: Copy a row from 3000 excel files stored in a folder and paste it in a new workboo

Re: Copy a row from 3000 excel files stored in a folder and paste it in a new workboo

Re: Copy a row from 3000 excel files stored in a folder and paste it in a new workboo

Re: Copy a row from 3000 excel files stored in a folder and paste it in a new workboo

Re: Copy a row from 3000 excel files stored in a folder and paste it in a new workboo

Re: Copy a row from 3000 excel files stored in a folder and paste it in a new workboo

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions