Extracting from many excel sheets to a master document, Help

[RoyLittle0]

Hi,

Please see the link below to my post in the General Excel Forum

http://www.excelforum.com/excel-gene...eadsheets.html

I have approximately 500+ workbooks which are growing in number on a weekly basis, I wont to be able to collate certain cells from each of the workbooks onto one master document and every time another workbook is added to the folder I can run the Master document and it will update.

I think I have the VBA to open each file and then close it again, but I need some guidance on how to copy the individual cells and then past them to the Master document in the correct place, then move down to the next row??

[HaHoBe]

Hi, RoyLittle0,

JBeaucaire WBs to 1 Sheet or Ron de Bruin Merge data from all workbooks in a folder and RDBMerge, Excel Merge Add-in for Excel for Windows might be good ressources.

Ciao,
Holger

[RoyLittle0]

Thanks Ciao

I have looked at the options and think that "WBs to 1 Sheet" is suitable but this code copies Ranges, what I need to do is many individual Cells and also to have a header row, i think the section I have extracted is what I need to alter

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[HaHoBe]

Hi, RoyLittle0,

what I need to do is many individual Cells and also to have a header row

Imagine you donīt know your workbook: how would you know what to alter and how? Maybe think about attaching a sample workbook with one or two sample sheets as they look and the cells which need to be copied and the desired result on another sheet.

Ciao,
Holger

[RoyLittle0]

Hi, RoyLittle0,


Imagine you donīt know your workbook: how would you know what to alter and how? Maybe think about attaching a sample workbook with one or two sample sheets as they look and the cells which need to be copied and the desired result on another sheet.

Ciao,
Holger

HaHoBe,

Both the Weekly Record 2013 Master Rev 1.xlsx (500+) which is where I want to take the data from and the FSR Master.xlsx (Post #6) that I will be collating the information on have been uploaded to the post in the General Forum, with the link on the first post, the FSR Master.xlsx shows what data I need and the corresponding cell on the Weekly Record 2013 Master Rev 1.xlsx where I need to get the information from.

Roy

[HaHoBe]

Hi, Roy,

no need to quote full posts if they are located directly above.

I downloaded both workbooks. As I understand it (please correct me here when Iīm wrong) you want to load the information from workbooks like Weekly Record 2013 Master Rev 1.xlsx into FSR Master.xlsx. You have inserted the cell addresses you want to copy and thatīs where I start having trouble: Column A for FSR states E12 while E12 in Weekly Record shows part of the job number. Iīm afraid I wonīt be of any help except here as the result wonīt tell me I have done everything correctly. So i think I show you how I would try to adjust the code from Jerry.

Origional part about copying is

Please Login or Register  to view this content.

Code is untested as I do have problems with adjusting to find what from where should be inserted:

Please Login or Register  to view this content.

And you would need to save the master workbook as a macro-enabled workbook with the suffix xlsm in order to hold the code (code should be inserted into a standard module).

Ciao,
Holger

[RoyLittle0]

I have created 11 workbooks all with data in cells A1 to A12, if I amend the code so that I am looking for cells A1 to A12 I get the below information in my master sheet, it seems to be looking at itself and copying only cell A1 repeatedly down for the exact number of workbooks it is looking at

Customer Machine Type Machine Serial No
Customer
Customer
Customer
Customer
Customer
Customer
Customer
Customer
Customer
Customer
Customer

You have inserted the cell addresses you want to copy and thatīs where I start having trouble: Column A for FSR states E12 while E12 in Weekly Record shows part of the job number.

You lost me completely when I read this, but looking a little deeper I realised I have uploaded the wrong file, the correct file is Field Service Record Spares 2013 Master Rev4 (2).xlsx Sorry, i'm having a bit of a senior moment

[HaHoBe]

Hi, Roy,

as I didnīt test the code I use a wrong variable as loop counter in the array - sorry for that.

What I did: copied both the macro enabled workbook FSR Master as well as three copies of the newly supplied workbook into a test folder. FSR Master was open, the following code was run

Please Login or Register  to view this content.

I changed the drive and the path for the folder to be identical with the workbook with code.

Ciao,
Holger