Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook names

**Arsham24** · 09-21-2012, 06:06 PM

Hello

I found this code (Copy All Sheets From Each Workbook in Folder to a Single Sheet in New Workbook) and I'd like to use it to get monthly reports through several daily reports.

Can someone help me to modify this code so that it shows the name of the workbook that the data has been derived from in a separate coloumn (r.g. column Z)?

Also, the current code pastes the formulas and values, but I just need the values of the formulas not the actual formulas.

And finally, I don't need the entire pages I just want to copy the cells A1:Y20 of each workbook.

Thanks,

Please Login or Register  to view this content.

**mike7952** · 09-21-2012, 06:34 PM

Untested but give this a try

Please Login or Register  to view this content.

**Arsham24** · 09-21-2012, 06:50 PM

Thanks Mike.
I tried the code; for all the workbooks (except the first or last one) it pastes only the first 2 raws. And I need the workbook name not the worksheet name.

**Arsham24** · 09-21-2012, 07:09 PM

I changed:

PHP Code:


                    .Cells(LR, "z").Resize(UBound(a)) = ws.Name

to

PHP Code:


                    .Cells(LR, "z").Resize(UBound(a)) = FileName.Name

and it works fine.
But still only first 2 raws of the workbooks are pasted.

**mike7952** · 09-21-2012, 07:36 PM

This works for me

Please Login or Register  to view this content.

**Arsham24** · 09-21-2012, 07:42 PM

THANK YOU.
I tried it with different test files and it worked fine.

Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook names

LinkBack

Thread Tools

Rate This Thread

Display

Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook names

Re: Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook n

Re: Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook n

Re: Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook n

Re: Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook n

Re: Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook n

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions