copy data from multiple files to a single

[arora]

Hi All,

I request you to please help me with this problem. I have also posted my problem on mr.excelforum.

The problem is like this

I test samples and data from these are analyzed using excel. I copy the relevant specimen data and results to worksheet 5 of every workbook for sake of simplicity.The data occupies cells A1 to A10 of Sheet5 for every specimen's workbook.

I store all workbooks in a folder. From these workbooks a Database is made.
The format of Database sheet requires that data from individual specimens are first placed in column D and then E, F, G... and so on.

I need to extract and copy data from all the workbooks in this folder to this Database workbook.

I understand it becomes easier if all workbooks are in the same folder and therefore I have done so in my case. Also, cells A1 to A10 of Sheet5 in each specimen workbook contains data which makes it simpler to select while copying.

Please help me with this VBA code !
I thank you all ,
Regards
arora

[royUK]

If you search the Forum, I have posted code to copy data from workbooks stored in a folder.

[arora]

Hello RoyUK

Thank you for your reply, Infact I used your and Dominic suggestion of making macro as an Add-In and then putting a menu to trigger it

I did tried your code and tried modifying it also. I was trying to use .Copy Destination function but I was unsuccessful.

The problem is also that first specimen data is to be copied to Column D (i.e cells D1 to D10) and then on in subsequent columns (E1 to E10, F1 to F10 and so on..)

I have also attached Database workbook that shows where data is being pasted. Here, data is copied first to column D and then further.

Can you please help in in modification of this code ...

Thank you again,
arora

[royUK]

If the attached workbook is the database then you will have problems. An excel database needs to be in a table format - one row for each data set.

[arora]

Hello RoyUK,

Does it means that I will not be able to copy a column of data ?

or may I can first copy data as a row and then transpose it to form a column !

I was thinking of copying as per the following scheme

for first sample's workbook :: cell A1 (in sheet5 of workbook) to D1 (in database), A2 to D2 ..... and A10 to D10
for second sample's workbook :: cell A1 (in sheet5 of workbook) to E1 (in database), A2 to E2 ..... and A10 to E10

and the count of number of files will then itself determine how many columns are to be filled. Is it not possible ?

Please consider..
regards
arora

[royUK]

Hello RoyUK,

Does it means that I will not be able to copy a column of data ?

or may I can first copy data as a row and then transpose it to form a column !

I was thinking of copying as per the following scheme

for first sample's workbook :: cell A1 (in sheet5 of workbook) to D1 (in database), A2 to D2 ..... and A10 to D10
for second sample's workbook :: cell A1 (in sheet5 of workbook) to E1 (in database), A2 to E2 ..... and A10 to E10

and the count of number of files will then itself determine how many columns are to be filled. Is it not possible ?

Please consider..
regards
arora

I can't follow what you mean, attach a sample data sheet.

[arora]

Hello RoyUK,

Thank you again for your reply,

I have attached 3 files for your reference, these are CT02, CT03 and database.

CT02 and CT03 are the usual specimen files containing data and from them data is copied to databse.xls

The sequence is

Step 1. from Sheet5 (cells A1 to A22) of file CT02.xls, column D (cells D8 to D29) in database is filled

Step 2. then from Sheet5 (cells A1 to A22) of file CT03.xls, column E (cells E8 to E29) in database is filled

Step 3. This way for every CT file in the folder a column in databse.xls is filled.

Thank you again for your help,
regards
arora

[arora]

Hello RoyUK,

Please Help !!! In case my not clear even now, I will again write in detail or try and simplify things ..

Please Help !!

regards
arora

[ravishankar]

Hi
save the databasenew file in the same folde as CT series and run the macro. it will pull data from col A of each file
Ravi