Macro copy data from multiple workbooks to master sheet

Hi,

need macro to copy data from multiple workbook to master sheets.

Hi Visha.

Post a sample file

A few questions -

Are these workbooks having the same headers? Are they stored in one folder? Is it just 1 sheet that needs to be copied or multiple sheets within the workbooks?

Does the master workbook already exist and data should be appended at the next available row of the master file or should the master workbook be created from scratch?

hi,Arlu

All these workbooks having the same headers and they stored in one folder and master file is open.

Hi,jraj

please find sample file

Should the data from the attached files be added from the next available row onwards or should the master file be cleared and then fresh data input?

Arlu

yes data file should be added from the next available row one below one, according to all sheets .

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

Arlu Thanks for all you help

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

hi,Arlu

your code has run but data has not been copy.

Did you copy the code to the Master file code window?

yes i have copied in master file window.

Did you change the file path in the code from

Please Login or Register  to view this content.

to the path where your files reside?

Also ensure that the master file is not in the same path where the files are stored.

Hi,Arlu

pls check after changing the path it not working

Please Login or Register  to view this content.

Its is showing Consolidation complete and Master workbook is Blank??

Change this line from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

P.S - Please do not bump the thread so many times.

Arlu still master file showing blank (Consolidation complete)

Can you upload your master file after running the macro? I will use the earlier file you upload as the "before running macro" file.

I ran the macro at my end and it works.

Please find attached

If you have put the macro in the master file, you should save it as a .xlsm file not .xlsx.

Path = z:\visha.h\courier folder for -all file

path = z:\visha.h\idbi for -courier report master


Hope now u help me

thanks

You are not understanding what i am saying. You have to save the macro in the Courier Report Master file and save the file as a .xlsm file. Check under the file save as options.

This is not the solution to the problem but just one point that i need to bring to your notice.

I am checking the code along with the files right now.

All your data is getting consolidated into the "TAT" sheet of your file which is hidden.

Try this updated code -

Please Login or Register  to view this content.

hi,Arlu

sorry to disturbing you again again

i have save file in .xslm but again showing (Consolidation complete) and file blank.

Did you read the comment in post 24 and try the updated code?

Sorry Arlu

I got it finally

Thanku so much once again.

I have a very similar problem to solve as visha_1984. I tried to use his excel sheets and data as a practice before setting up my own files. However, I'm getting a "run time error 9, subscript out of range" and the code is not drawing the data into the master workbook. The debugger highlights the line "Set sourceData = sourceBook.Worksheets ("MASTER").
Is there anything I need to change in the code below to get this to work? I'm pretty new to VBA programming so if someone could include an explanation as to why I'm getting the error along with the solution that would be greatly appreciated. Thank you.

Please Login or Register  to view this content.

Ghoststark21,

Check if the name of the source sheet is named as "MASTER". If not, then change it in the code to whatever is the name of your source sheet.

u767,

Your data might be differently formatted. Its better you create a thread of your own and attach a sample file. You can link back to this thread by providing a link of this thread in your thread. I will help you there.