vba code to copy data from a sheet
Dear experts,
I want a vba code which can perform the following:
1. Copy the data from a range in a sheet (to be named) from a excel file in the folder on to a new sheet. The column A will include the name of the file from which the data has been sourced.
2. The vba will append the data (one below the earlier) from the various sheets in the folder.
Thank you
I want a vba code which can perform the following:
1. Copy the data from a range in a sheet (to be named) from a excel file in the folder on to a new sheet. The column A will include the name of the file from which the data has been sourced.
2. The vba will append the data (one below the earlier) from the various sheets in the folder.
Thank you
What you are asking for should not be too difficult but we need more detail
eg Number of workbooks that need to be read from, Named range identifer, sheet number named range is on, number of columns etc
If you could you please post a couple of example workbooks with dummy data (same format) it will provide a lot of the information we need
eg Number of workbooks that need to be read from, Named range identifer, sheet number named range is on, number of columns etc
If you could you please post a couple of example workbooks with dummy data (same format) it will provide a lot of the information we need
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and here is the free version from chandoo web
http://chandoo.org/wp/2012/04/09/consolidate-data-from-different-excel-files-vba/
http://chandoo.org/wp/2012/04/09/consolidate-data-from-different-excel-files-vba/
ASKER
Hi Simonadept,
Thank you for your response.
Is it a single spreadsheet you want to retreive data from?
YeS,
Do you want to prompt the user to browse and select the file each time?
No, a specific cell range within a sheet will be hardcoded in the vba code. But ofcourse the cell range should be amendable in the code to tailor the vba to each of the specific situations
How is the worksheet within the workbook to be identified - i.e. always sheet1 or a known sheet name?
Yes, a specific sheet which will be standard across all the excel files
Is it a single row range? - assume so if you want the source file name to go in column A.
As specificied it will be a range like say c2:d55
Further as this an accumulation of similar nature of data from several of the spreadsheets (files), the individual ranges copied should be pasted one below the other. As a identifier of the data set, column A will host the file name. In other words, say
Say range A2: A55 will have file name as ‘xyz23march2014’ and cell range c2:d55 will include the pasted data
Next A56:109 will have file name as ‘xyz24march2014’ and cell range c56:d109 will include the pasted data and so on
Thank you for your response.
Is it a single spreadsheet you want to retreive data from?
YeS,
Do you want to prompt the user to browse and select the file each time?
No, a specific cell range within a sheet will be hardcoded in the vba code. But ofcourse the cell range should be amendable in the code to tailor the vba to each of the specific situations
How is the worksheet within the workbook to be identified - i.e. always sheet1 or a known sheet name?
Yes, a specific sheet which will be standard across all the excel files
Is it a single row range? - assume so if you want the source file name to go in column A.
As specificied it will be a range like say c2:d55
Further as this an accumulation of similar nature of data from several of the spreadsheets (files), the individual ranges copied should be pasted one below the other. As a identifier of the data set, column A will host the file name. In other words, say
Say range A2: A55 will have file name as ‘xyz23march2014’ and cell range c2:d55 will include the pasted data
Next A56:109 will have file name as ‘xyz24march2014’ and cell range c56:d109 will include the pasted data and so on
ASKER
Hi Simonadept,
Please find attached an example
Further I would like one complication to this process. The excel could be located in the subfolder level. for instance
Main folder (this has only sub-folders and no spreadsheets)
->Sub-folder 1 (this has only sub-folders and no spreadsheets)
---> Sub-folder 1.1 (this has only spreadsheets, say 4 spreadhseets)
The Macro should detect any folder name starting iwth 'xyz' and seek to perform the code only on these excel files.
Hope it is clear now.
Apologies for not being clear in the first instance.
Thank you
Please find attached an example
Further I would like one complication to this process. The excel could be located in the subfolder level. for instance
Main folder (this has only sub-folders and no spreadsheets)
->Sub-folder 1 (this has only sub-folders and no spreadsheets)
---> Sub-folder 1.1 (this has only spreadsheets, say 4 spreadhseets)
The Macro should detect any folder name starting iwth 'xyz' and seek to perform the code only on these excel files.
Hope it is clear now.
Apologies for not being clear in the first instance.
Thank you
ASKER
Michael74, sorry I missed to include your name in my address.
ASKER
ProfessorJimJam,
the chandoo's solution does include a template where we can capture the source file name but not the sheet name.
thank you
the chandoo's solution does include a template where we can capture the source file name but not the sheet name.
thank you
ASKER
boro,
I did not update you.
Two issues cropped up to use your suggestions
1. A particular licence on my pc for ms office has expired, due to which I am not able to install the add-in
2. Even if I could install the add-in I am not sure my pc would let me turn-on the macro trust certification in excel options.
Hence I am request for a vba solution.
I did not update you.
Two issues cropped up to use your suggestions
1. A particular licence on my pc for ms office has expired, due to which I am not able to install the add-in
2. Even if I could install the add-in I am not sure my pc would let me turn-on the macro trust certification in excel options.
Hence I am request for a vba solution.
why didn't you use the tool from officelike?
see attached. i created new file Book1 and it works. added info and it works.
Book1.zip
see attached. i created new file Book1 and it works. added info and it works.
Book1.zip
ASKER
ProfessorJimJam,
Thank you. Does it handle range or only cell at a time.
I need a macro which can handle the range.
thank you
Thank you. Does it handle range or only cell at a time.
I need a macro which can handle the range.
thank you
It can handle range too
Try and let me know
Try and let me know
Instead of single address put for example A1:G25.
ASKER
can you please attach the other files as well? I can see that you did not change the source worksheet name in the file.
as I can see it is still the old sheet name, as I sent it.
as I can see it is still the old sheet name, as I sent it.
ASKER
Prof.
Thank you for response.
I will get back to my job in 3 hrs and then I will try to reperform this utilityou have given and gcomeback with the result.
But I think this should be a simplace macro to develop. Probably because of lack of knowledge I am underestimating the effort.
Thank you
Thank you for response.
I will get back to my job in 3 hrs and then I will try to reperform this utilityou have given and gcomeback with the result.
But I think this should be a simplace macro to develop. Probably because of lack of knowledge I am underestimating the effort.
Thank you
hi Excel Learner,
1. A particular licence on my pc for ms office has expired, due to which I am not able to install the add-in.
I am not aware of any licenses being required for installing addins within Excel. To help my general understanding, what is the name of the license?
Is the license for an anti-virus/malware programme or for Winzip/WinRAR (to unzip the addin file)?
Are using a Trial version of MS Office?
What steps do you take when you try to install the addin?
2. Even if I could install the add-in I am not sure my pc would let me turn-on the macro trust certification in excel options.
Hence I am request for a vba solution.
For many intents & purposes (esp in Excel), "macros" & "VBA" are just different words for the same thing. The RDB Merge addin is an "xlam" (excel addin file) which is just a type of excel file that can contain VBA code. An advantage of using an addin over a normal Excel file containing code is that once it is installed it "opens silently in the background" when excel is opened & doesn't clutter up your screen by appearing as another visible file.
Rob
1. A particular licence on my pc for ms office has expired, due to which I am not able to install the add-in.
I am not aware of any licenses being required for installing addins within Excel. To help my general understanding, what is the name of the license?
Is the license for an anti-virus/malware programme or for Winzip/WinRAR (to unzip the addin file)?
Are using a Trial version of MS Office?
What steps do you take when you try to install the addin?
2. Even if I could install the add-in I am not sure my pc would let me turn-on the macro trust certification in excel options.
Hence I am request for a vba solution.
For many intents & purposes (esp in Excel), "macros" & "VBA" are just different words for the same thing. The RDB Merge addin is an "xlam" (excel addin file) which is just a type of excel file that can contain VBA code. An advantage of using an addin over a normal Excel file containing code is that once it is installed it "opens silently in the background" when excel is opened & doesn't clutter up your screen by appearing as another visible file.
Rob
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ExcelLearner,
can you give us feedback, if the provided solution did not work? so that i come up with alternative?
can you give us feedback, if the provided solution did not work? so that i come up with alternative?
ASKER
Everyone,
I am back in action, will give the feedback over today/tomrrow and close the question.
I am back in action, will give the feedback over today/tomrrow and close the question.
ASKER
My office laptop has issues with the macro access.
But thank you all for the several thoughts, time and inputs.
But thank you all for the several thoughts, time and inputs.
hi Excel Learner,
I feel that we haven't got to root cause of your issue yet. Since you have assigned points for this question (although I don't feel I deserve any), do you want to raise a new question to see if anyone can help resolve your "issues with the macro access"?
Rob
I feel that we haven't got to root cause of your issue yet. Since you have assigned points for this question (although I don't feel I deserve any), do you want to raise a new question to see if anyone can help resolve your "issues with the macro access"?
Rob
ASKER
Boro,
You are correct, honestly what I have done is not a professional way of treating of your (expert's precious) time when you try to help resolve my issues.
I wish to start with a new question on this subject today.
Kindly help.
Thank you
You are correct, honestly what I have done is not a professional way of treating of your (expert's precious) time when you try to help resolve my issues.
I wish to start with a new question on this subject today.
Kindly help.
Thank you
ASKER
Boro,
Could you please spare some time and help me on ID: 28624651.
Thank you
Could you please spare some time and help me on ID: 28624651.
Thank you
hi Excellearner,
I can't find that question. Can you please post the whole URL?
Rob
I can't find that question. Can you please post the whole URL?
Rob
ASKER
Rob/broro,
I think the admin has deleted that question.
I am happy to create a new question once agian.
but here is the content
Dear experts
Kindly provide a macro which can copy select range for a spreadsheet at a time but repeat this exercise over several sheets listed in the range
Inputs to macro:
Range for the several sheets provided above
What is expected from the macro
1. Selected a path from column starting with the first folder from row 2
2. Open the file
3. Do not activate( update) for linkages
4. copy the range into the destination sheet from column B (Pattern is shown in sheet 'destinate sheet' in this file), then leave two blank before copying the next range
5. Column A will include the file name (details in the column above)
6. close the file in the current folder without saving
7. Move to the next file in row 3 above and repeat exercise from point 1 onward and complete this iteration untill all the rows have been completed
8. Provide message when all the actions have been executed
All linked files to the file mentioned above will be in the same folder and there are no linked to files outside the folder.
File-folder-range-details-for-copy---mac
Update-msg-screen-dump.docx
I think the admin has deleted that question.
I am happy to create a new question once agian.
but here is the content
Dear experts
Kindly provide a macro which can copy select range for a spreadsheet at a time but repeat this exercise over several sheets listed in the range
Inputs to macro:
Range for the several sheets provided above
What is expected from the macro
1. Selected a path from column starting with the first folder from row 2
2. Open the file
3. Do not activate( update) for linkages
4. copy the range into the destination sheet from column B (Pattern is shown in sheet 'destinate sheet' in this file), then leave two blank before copying the next range
5. Column A will include the file name (details in the column above)
6. close the file in the current folder without saving
7. Move to the next file in row 3 above and repeat exercise from point 1 onward and complete this iteration untill all the rows have been completed
8. Provide message when all the actions have been executed
All linked files to the file mentioned above will be in the same folder and there are no linked to files outside the folder.
File-folder-range-details-for-copy---mac
Update-msg-screen-dump.docx
hi Excellearner,
If it is possible please delete the content of your previous message, as it doesn't belong in this thread. It belongs in a question of its own.
By viewing your History via your User Profile I can see that you have created two questions that are very similar to what you have posted in the last message (ID: 40632636):
- Macro for deleting values in several sheets (Q_28624034)
- Macro to copy and paste (as values) from several files listed (Q_28624121)
My understanding of the purpose of this site, & my reason for being here, is to voluntarily help people with specific issues. The above questions appear to be entire projects rather than specific issues. So I agree with the comments made by Aikimark & Phillip Burton comments (ID: 40632383ID: 40632383 & ID: 40632644 resp.) that you should investigate the "hire me" option which some experts have. I don't have this option & I'm not actually sure where it is displayed but I'm sure it is easy to find if you look for it.
I also feel that the questions are so similar that an Excel Learner, such as yourself, could combine the common features & draw techniques from the answers to your previous questions to attempt your projects. Then you can raise questions for the aspects which you can't do yourself ie specific issues. Demonstrating that you've made an attempt by including your attempted code in your questions/uploaded files could very well encourage experts to be more enthusiastic participants in your future questions.
I look forward to seeing your attempts, progress & development in future questions.
Good luck!
Rob
If it is possible please delete the content of your previous message, as it doesn't belong in this thread. It belongs in a question of its own.
By viewing your History via your User Profile I can see that you have created two questions that are very similar to what you have posted in the last message (ID: 40632636):
- Macro for deleting values in several sheets (Q_28624034)
- Macro to copy and paste (as values) from several files listed (Q_28624121)
My understanding of the purpose of this site, & my reason for being here, is to voluntarily help people with specific issues. The above questions appear to be entire projects rather than specific issues. So I agree with the comments made by Aikimark & Phillip Burton comments (ID: 40632383ID: 40632383 & ID: 40632644 resp.) that you should investigate the "hire me" option which some experts have. I don't have this option & I'm not actually sure where it is displayed but I'm sure it is easy to find if you look for it.
I also feel that the questions are so similar that an Excel Learner, such as yourself, could combine the common features & draw techniques from the answers to your previous questions to attempt your projects. Then you can raise questions for the aspects which you can't do yourself ie specific issues. Demonstrating that you've made an attempt by including your attempted code in your questions/uploaded files could very well encourage experts to be more enthusiastic participants in your future questions.
I look forward to seeing your attempts, progress & development in future questions.
Good luck!
Rob
Do you want to prompt the user to browse and select the file each time?
How is the worksheet within the workbook to be identified - i.e. always sheet1 or a known sheet name?
Is it a single row range? - assume so if you want the source file name to go in column A.