I need a macro for copying the data of all excel files into one sheet of a excel file.

[pavan_yuvaraj]

hey guys i am having 8 to 12 excel files i need all the data of those excel files in a folder to paste in a single excel file in single sheet

[arlu1201]

So the data should be copied one below the other right?

Do you want the filename to be shown, to reflect from which file the data was copied?

Do the source files have only 1 sheet with data or multiple sheets per file?

What is the last column containing data?

[pavan_yuvaraj]

I have 8 to 12 excel files with single sheet and I want to merge all those files into one excel file with 1 tab

Yes the data should be copied one below the other

Yes i want the file name to be shown, to reflect from which file the data was copied

Yes the source files have only 1 sheet

The last column contains also the data for example

hzn6l5:x:21423:21019:Srinivas Kanduri:/model/adr_home/hzn6l5:/bin/sh
tz61kf:x:21424:21019:Satish Padiyar:/model/adr_home/tz61kf:/bin/sh
rzd766:x:21425:21019:Michael Sullivan:/model/adr_home/rzd766:/bin/sh
zz04np:x:21078:21019:Todd Justman:/model/adr_home/zz04np:/bin/sh
wzmbqc:x:21079:21019:Padmapriya Rajagopalan:/model/adr_home/wzmbqc:/bin/sh
pz8y5p:x:21085:21019:Nassima Chafai:/model/adr_home/pz8y5p:/bin/sh

[arlu1201]

In the last question, i meant to ask the column address of the last column having data. Like column M or column X.

[pavan_yuvaraj]

Hey these are the sample files

Thanks

[arlu1201]

Your data is all in column A. Do you want the data to be split into columns after consolidation?

[pavan_yuvaraj]

I think its better if u think column X is the last one.

Yes that would be gr8 if it splits after consolidation or else i will do it manually as per my requirement if i need a code that to merge or append all the files into on xl sheet .
And it would be gr8 if u have gven me the steps how to add the code into a excel sheet

Thanks

[arlu1201]

But how column X? All your data is in column A.

[pavan_yuvaraj]

yes All our data will be in column A only

can u help me on this macro

[arlu1201]

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 Tools | Macro | Macros
Select a macro in the list, and click the Run button

[pavan_yuvaraj]

Hey thanks for sending me the code and i am getting the following error

do u want me to place all the files in one folder.

and do u want me to change the path in code

Please mark in red what is to be changed?

Thanks for ur quick reply.....

[arlu1201]

What error are you getting?

Yes, all files should be placed in one folder. Change the path i have marked bold in the code above.

[pavan_yuvaraj]

Hi,
i have changed the path and this is the screen shot so pease help me in this regard and i kept all the files in a fder as u said.

[arlu1201]

Sorry about that. I have edited the code in post 10 to reflect the changes.

Please try it now and let me know.

[pavan_yuvaraj]

Thanks for editing but now i am getting this error and its not getting consolidated

i am doing as per this document, please let me know wether this flow is correct or not.

[arlu1201]

U dont need to open the source files. The macro will open, copy and close each file. Only open the file containing the macro and run it. Ensure that the macro file is not in the same folder as the other files.

[pavan_yuvaraj]

Hey Arlette,

I have success fully able to execute the code thanks for the help,

Its Really a very quick response and u have helped me a lot,

Thank you so much for helping me.

Urs Pavan..

[Cutter]

@ pavan_yuvaraj

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.