Macro to extract data from files
Posted on 2011-09-23
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. Close the current file from where the data has been copied
6. Then open the next file and the repeat the steps 2 to 5,
7. But macro should copy the range data adjacent to the range copied from file 1 and so on
8. 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.