JimColumbiaSC
New Member
- Joined
- Feb 12, 2014
- Messages
- 1
Hi Guys,
I have never posted before but I am hoping that someone will know how to knock this out. My admin is going crazy opening all these files and then cutting and pasting little snippets into one merged file! I have to believe a macro type VBA program can help here.
We need a <acronym>VBA</acronym> Macro for Excel 2010 that opens all the Excel files in one directory … goes to a specific tab within each file … and then copies the rows of data into one consolidated merged output file. The number of lines of data in each source instance is variable. The destination it needs to get copied to just becomes a long running list of the data found in all the source files.
Conditions
========
1. Each Excel source file has multiple and varied numbers of worksheets (tabs); but we are only interested in data lines that appear on the “Software” tab in each of these files.
1a. One exception may be there is no “Software” tab in the source file so we just skip it.
2. Each source “Software” tab may have data starting in row 2 and going down to the last row where data is entered
2a. Row one should be the same header each time so we don’t need to copy that line
2b. Sometimes row 2 itself may be empty which means there is no data from that file and we can skip onto the next file.
4. When data does exist it always starts in Column A and ends in Column H.
5. In the final Merged file, it would be nice if the corresponding cell in column J could contain the name of the source file where that line of data came from … but that is a nicety.
6. All files need to be closed nicely so that they can be edited by their owners again later.
I would greatly appreciate it if someone could code this up. My admin would love it as well.
Thanks.
I have never posted before but I am hoping that someone will know how to knock this out. My admin is going crazy opening all these files and then cutting and pasting little snippets into one merged file! I have to believe a macro type VBA program can help here.
We need a <acronym>VBA</acronym> Macro for Excel 2010 that opens all the Excel files in one directory … goes to a specific tab within each file … and then copies the rows of data into one consolidated merged output file. The number of lines of data in each source instance is variable. The destination it needs to get copied to just becomes a long running list of the data found in all the source files.
Conditions
========
1. Each Excel source file has multiple and varied numbers of worksheets (tabs); but we are only interested in data lines that appear on the “Software” tab in each of these files.
1a. One exception may be there is no “Software” tab in the source file so we just skip it.
2. Each source “Software” tab may have data starting in row 2 and going down to the last row where data is entered
2a. Row one should be the same header each time so we don’t need to copy that line
2b. Sometimes row 2 itself may be empty which means there is no data from that file and we can skip onto the next file.
4. When data does exist it always starts in Column A and ends in Column H.
5. In the final Merged file, it would be nice if the corresponding cell in column J could contain the name of the source file where that line of data came from … but that is a nicety.
6. All files need to be closed nicely so that they can be edited by their owners again later.
I would greatly appreciate it if someone could code this up. My admin would love it as well.
Thanks.
