Want a Macro in Excel to Compile data from Several individual files into a separate Master

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Dear mmmarks,

I want the column D from the source files to be copied to the sheet1 of the master file in successive columns.
and for the identification of the source files i wanted column header to be source file's file or sheet name if possible

---------- Post added at 09:17 PM ---------- Previous post was at 09:12 PM ----------

Dear arlu1201

thank you for the elaborate procedure and the codes, i made the macro to run by making adjustment to 'mypath',
the macro runs smoothly but there might be some glitch coz the master file is blank every time.
while the macro is running, i can see that it is copying all the content of the source file to the master and then again being replaced by next copy and in the end all is blank.

Do you have a sample file that you can upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Dear arlu1201

i have attached one of the source files that i am trying to copy into the master file
the data is of one of the record stations named "DS010420.00" and the filename is "DS010420.00.xls"
i wanted to copy the data in column D of the file and several other similar files to one master xls.
please feel free to ask me anything more in this regard and i appreciate your help.
Thank you.

Did you put the macro in the master file or in the source file?

I am sorry if i didnt mention it earlier but the macro needs to go into your master file.