How to read from different excel files in a folder
Hello All
Please try this macro Parsing.xlsm
In this macro taking current workbook as input it is creating new workbook with different sheets. In the same manner I need macro taking several files (having same title row) as input and finally creating master workbook with different sheets as above.
Please help me out of this. I am struggling with this from many days.
Thanks in advance
PS: Take sample multiple sheets as
file1.xlsx
file2.xlsx
file3.xlsx
So you want each of your files to show up as a separate sheet in one master workbook?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Yeah, but the sheets should be created based one column in all files. All the corresponding rows in every file should be copied in to respective sheet of master book.
Please explain a little more in detail. What do you mean by your reply in post 3?
Sure Arlu, I have many excel files with same title row.
Now by taking these files as input I need to create a new master workbook. Such that the sheets in the master workbook are created based on a particular column in all files. here say Column 2 i.e Date
for example Test1.xls and Test2.xls has following data
Test1.xls
Name Date
Ramu 2-1-2013
Ravi 2-1-2013
Jacob 3-1-2013
Julie 3-1-2013
Test2.xls
Name Date
Mary 2-1-2013
Rakhi 2-1-2013
Bourne 3-1-2013
Based on this input Master workbook should create two sheets named as 2-1-2013 and 3-1-2013
After this the corresponding rows should be copied to respective sheets as below.
Sheet1: 2-1-2013
Name Date
Ramu 2-1-2013
Ravi 2-1-2013
Mary 2-1-2013
Rakhi 2-1-2013
Sheet2: 3-1-2013
Name Date
Jacob 3-1-2013
Julie 3-1-2013
Bourne 3-1-2013
Let me know if you have any questions.
Got it now. Should the master file be newly created each time you run the macro or do you want an existing master file to be updated?
Master workbook should be newly created each time when we run a macro.
Creating two macros for this scenario is also appreciated.
One for combining all data into single sheet and another macro for splitting the combined data into sheets.
The 2nd macro should have the data split into separate sheets in the same file or separate workbooks?
In the same file.
Finally I did it. All credit goes to Jerry. Thank you very much for your support and helping me in all ways.
Original macro code: https://sites.google.com/a/madrocket...s-to-one-sheet
I slightly modifies the above code as:
Macro to consolidate all excel files into single sheet:
Please Login or Register to view this content.
Macro to split consilidated data into sheets:
Please Login or Register to view this content.
Last edited by JBeaucaire; 12-27-2019 at 10:19 PM.
Thanks Excel forum.
Last edited by jsrakesh4u; 02-22-2013 at 05:12 AM.
Since BOTH of the base macros you edited to create your final working macro were from my code site, I'm only "slightly" offended by your assertion that you "did it yourself". I would use caution when you make small tweaks to an existing macro, remove the author's name from the top then post it as work you "did yourself".
Certainly you see how that could send the wrong message to people.
Of course, the code is there for you to use, absolutely. Just take care how you present the finished work to others.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
I am totally new to visual basics and macros. I made myself to understand the code and kept many efforts and sleepless nights in customizing this macro. Anyway in getting this macro I may have said "myself", But the whole credit goes to jerry which gave me a great space to understand VBA programming in a short time.
No need to get serious about this thing Jerry. I am extremely sorry If my words have hurt you.
No worries, the code is posted for the use for which you employed it. My feelings aren't an issue, but your credibility is. Read my words of caution again and take them for what they say, that's all I ask.
See you on the next project, we'll be here.
Sure sure.. Have a nice day....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks