Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)
Course Of The Month
8 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Macro to extract data from a range within a sheet name from several files in a folder
Posted on 2011-09-25
Dear experts,
I have a folder on my laptop/Desktop, which has about 20 excel files in both 2003 and 2007 version. Each file has several sheets, but consistently named. Within each sheet there are several data arrays.
What i need is a macro which will do the following
1. Go the folder and select each sheet at a time.
a. The macro should have a location where i can feed the path
2. When it selects the first file, the macro should seek a sheet
a. The macro should have a location where i can feed the sheet name
3. When it selects the target sheet, the macro should seek a range
4. Then copy the range on to a new sheet.
5. Copy the name of the sheet in row 1 on all the column the data is being copied. For instance if data is being copied in column A B and C then the name of the source file should be reflected in cell A1, B1 and C1. the data will be copied from row 2 onwards.
6. Close the current file from where the data has been copied
7. Then open the next file and the repeat the steps 2 to 5,
8. But macro should copy the range data adjacent to the range copied from file 1 and so on
9. For example if we have copied data on the new sheet in column A:b, then the data from second file should be copied in range C;D and so on.
I should be able to change the range details of the data to be copied from the files. In other words, i need to be able to change the range details in the macro so that i can use it several times.
Thank you,
I have a folder on my laptop/Desktop, which has about 20 excel files in both 2003 and 2007 version. Each file has several sheets, but consistently named. Within each sheet there are several data arrays.
What i need is a macro which will do the following
1. Go the folder and select each sheet at a time.
a. The macro should have a location where i can feed the path
2. When it selects the first file, the macro should seek a sheet
a. The macro should have a location where i can feed the sheet name
3. When it selects the target sheet, the macro should seek a range
4. Then copy the range on to a new sheet.
5. Copy the name of the sheet in row 1 on all the column the data is being copied. For instance if data is being copied in column A B and C then the name of the source file should be reflected in cell A1, B1 and C1. the data will be copied from row 2 onwards.
6. Close the current file from where the data has been copied
7. Then open the next file and the repeat the steps 2 to 5,
8. But macro should copy the range data adjacent to the range copied from file 1 and so on
9. For example if we have copied data on the new sheet in column A:b, then the data from second file should be copied in range C;D and so on.
I should be able to change the range details of the data to be copied from the files. In other words, i need to be able to change the range details in the macro so that i can use it several times.
Thank you,
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
-
5
3
16 Comments
hi,
I'm off to bed so I won't be able to post any follow up comments for a while, but when I saw your question I thought that you may find Ron DeBruin's free add-in useful. I haven't checked if it does exactly what you want, but I'm sure it is very close - see the below link for screenshots & plenty of details:
http://www.rondebruin.nl/merge.htm
hth
Rob
I'm off to bed so I won't be able to post any follow up comments for a while, but when I saw your question I thought that you may find Ron DeBruin's free add-in useful. I haven't checked if it does exactly what you want, but I'm sure it is very close - see the below link for screenshots & plenty of details:
http://www.rondebruin.nl/merge.htm
hth
Rob
Hi
Here's a little tool I have which does something similar to what you're asking for. Have a go with this and see whether it is good enough. If not, let me know what changes you need to it.
Instructions:
- enter the path for the folder you want to process in A1
- enter the range names that you want to read in B1:IV1
- press the churn button
- each workbook gets one row, and the flename of the worksheet goes in column A
Book10.xls
Here's a little tool I have which does something similar to what you're asking for. Have a go with this and see whether it is good enough. If not, let me know what changes you need to it.
Instructions:
- enter the path for the folder you want to process in A1
- enter the range names that you want to read in B1:IV1
- press the churn button
- each workbook gets one row, and the flename of the worksheet goes in column A
Book10.xls
Jan24,
thank you for the comment.
I do not have range name, rather i want to range like 'sheet1'!b2:c6.
Kindly let me know if the vba could be amended to incorporate the above requirement.
thank you,
thank you for the comment.
I do not have range name, rather i want to range like 'sheet1'!b2:c6.
Kindly let me know if the vba could be amended to incorporate the above requirement.
thank you,
Here it is again set up to do 'sheet1'!b2:c6. Try it and let me know if it works!
Book10a.xls
Book10a.xls
hi Excel Learner,
Have you looked the link I previously posted?
It is a soundly developed & tested addin by a well known excel wizz and I believe the screenshot on the page shows that it will do exactly what you have asked in your last post (# 36716169).
Jan24, I glanced through your code and I think the OP wants the layout to be transposed, relative to how your code currently makes it, esp when reading through the initial points # 5 to #9.
I'm curious, why do you use ".evaluate" rather than writing directly to the cell (or cells, for more efficiency - ie minimise "the hits on the spreadsheet")?
Rob
Have you looked the link I previously posted?
It is a soundly developed & tested addin by a well known excel wizz and I believe the screenshot on the page shows that it will do exactly what you have asked in your last post (# 36716169).
Jan24, I glanced through your code and I think the OP wants the layout to be transposed, relative to how your code currently makes it, esp when reading through the initial points # 5 to #9.
I'm curious, why do you use ".evaluate" rather than writing directly to the cell (or cells, for more efficiency - ie minimise "the hits on the spreadsheet")?
Rob
The .Evaluate was so that it was flexible enough to use cell references, range names or local range names, or formulae that generate a range.
Ahh, that makes sense - thanks, you've taught me a new trick :-)
I had read in the past that Evaluate is flexible, but I didn't know what made it flexible - now I do!
It certainly looks like it could be useful in other situations too & makes for shorter code than we'd otherwise get if we were to test the input for each possible option using Typename.
thanks
Rob
I had read in the past that Evaluate is flexible, but I didn't know what made it flexible - now I do!
It certainly looks like it could be useful in other situations too & makes for shorter code than we'd otherwise get if we were to test the input for each possible option using Typename.
thanks
Rob
Jan 24,
I have put the de4tails, when i clicked the button, the macro did not activate there was no error message either.
Is this macro very difficult. I have been waiting for four days now.
Thankyou
I have put the de4tails, when i clicked the button, the macro did not activate there was no error message either.
Is this macro very difficult. I have been waiting for four days now.
Thankyou
hi Excel Learner,
I have been waiting for a few days too...
Have you looked the link I previously posted?
http://www.rondebruin.nl/merge.htm
This is a FREE soundly developed & tested addin by a well known excel wizz (a certified "Microsoft Most Valued Professional") and I believe the screenshot on the page shows that it will do exactly what you have asked in your earlier post (# 36716169).
So that I can be more effective at helping others in the future (ie to recognise why the addin isn't suitable), can you please test it and tell me how it fails to meet your needs?
I look forward to your response,
Rob
I have been waiting for a few days too...
Have you looked the link I previously posted?
http://www.rondebruin.nl/merge.htm
This is a FREE soundly developed & tested addin by a well known excel wizz (a certified "Microsoft Most Valued Professional") and I believe the screenshot on the page shows that it will do exactly what you have asked in your earlier post (# 36716169).
So that I can be more effective at helping others in the future (ie to recognise why the addin isn't suitable), can you please test it and tell me how it fails to meet your needs?
I look forward to your response,
Rob
Broro183 brother,
Sorry that my language was inappropriate in my earlier response.
Bulls eye. Your solution did the job perfectly. I am very happy. Sorry for not giving the appropriate time to your comment.
The only reason this has happened was because, I was looking for vba based solution.
But your solution did the job.
Now do you think i can close the question though the solution was not vba driven.
Kindly comment.
Thank you
Sorry that my language was inappropriate in my earlier response.
Bulls eye. Your solution did the job perfectly. I am very happy. Sorry for not giving the appropriate time to your comment.
The only reason this has happened was because, I was looking for vba based solution.
But your solution did the job.
Now do you think i can close the question though the solution was not vba driven.
Kindly comment.
Thank you
hi everyone,
ExcelLearner, Phew!
I'm pleased it helped - I was really struggling to see what I might have overlooked. RDB's work is very helpful for me & you may find other good stuff on his site too.
I'm not sure how the E-E Mod's decide exactly what zone a question is held in but...
fyi, this is still a "vba driven" solution, it's just that RDB has done all the work and the vba is now "locked inside" the addin file.
Jan24, sorry about pulling this one out from under you before/after your work with the .
I think your solution is worthwhile, however if you check out RDB's addin you'll probably agree with me that his, existing, addin is sooo much more flexible.
ExcelLearner,
I think you can close the question. It's your choice what you do with the points. You can consider that the transparency of Jan's work allowed you the opportunity to learn... did you?
In contrast, my link provides what you need and more, BUT you can't learn from it as it is a "black box".
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
ExcelLearner, Phew!
I'm pleased it helped - I was really struggling to see what I might have overlooked. RDB's work is very helpful for me & you may find other good stuff on his site too.
I'm not sure how the E-E Mod's decide exactly what zone a question is held in but...
fyi, this is still a "vba driven" solution, it's just that RDB has done all the work and the vba is now "locked inside" the addin file.
Jan24, sorry about pulling this one out from under you before/after your work with the .
I think your solution is worthwhile, however if you check out RDB's addin you'll probably agree with me that his, existing, addin is sooo much more flexible.
ExcelLearner,
I think you can close the question. It's your choice what you do with the points. You can consider that the transparency of Jan's work allowed you the opportunity to learn... did you?
In contrast, my link provides what you need and more, BUT you can't learn from it as it is a "black box".
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Broro brother,
I have raised another question with ID: 27362399, this particuarly refers to the situation for my wife.
I could not find any proper coaching places in London which would train people in Ms access and vba, hence i was exploring online tutorials and sending her to India.
can you please comment in that question, if there is a place where they would take people on a voluntary basis and train them or charge them for training in these skills.
Appreciate your help on that.
Thank you
I have raised another question with ID: 27362399, this particuarly refers to the situation for my wife.
I could not find any proper coaching places in London which would train people in Ms access and vba, hence i was exploring online tutorials and sending her to India.
can you please comment in that question, if there is a place where they would take people on a voluntary basis and train them or charge them for training in these skills.
Appreciate your help on that.
Thank you
Broro brother,
One observation on the solution you suggested.
I am not quite sure if i can provide two ranges in the addin.
Where as in vba we can make the query to select two or more distinct ranges with an query.
thank you,
One observation on the solution you suggested.
I am not quite sure if i can provide two ranges in the addin.
Where as in vba we can make the query to select two or more distinct ranges with an query.
thank you,
hi Excel Learner,
Have you looked at the [Tips] button beside the "Fixed range" section?
I'm not sure if this will do what you want or not, you'll have to try it and see...
Even if it doesn't exactly do what you want, you may still find it faster to run RDB's addin twice (ie once for each range) and consolidate the results.
If you still want a different solution, a Moderator would be able to tell you the best course of action, which may be to ask a new question, or it may be to re-open this thread & continue with Jan24's work.
I'll post in your other thread about MS Access & VBA training.
Rob
Have you looked at the [Tips] button beside the "Fixed range" section?
I'm not sure if this will do what you want or not, you'll have to try it and see...
Even if it doesn't exactly do what you want, you may still find it faster to run RDB's addin twice (ie once for each range) and consolidate the results.
If you still want a different solution, a Moderator would be able to tell you the best course of action, which may be to ask a new question, or it may be to re-open this thread & continue with Jan24's work.
I'll post in your other thread about MS Access & VBA training.
Rob
Featured Post
Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s
ManageEngine
webinar, where attendees received a comprehensive look at the ma…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 1 hour left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.