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.