Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
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,
-
6
-
5
3
14 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
Active 1 day ago
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
Active 1 day ago
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
Active 1 day ago
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...
Active 1 day ago
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
Active 1 day ago
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
Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering. This article seeks by illustration …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other.
As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month8 days, 16 hours left to enroll
595 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.