Write VBA code to copy paste the data from multiple excels
Budget: $10 – $12 USD
I have folders for 22 years, each of these folders has internal folders dedicated to months, and in each month folder there are Excel files for daily values.
There are several sheets in each Excel file, and I need only certain values from one of the sheets.
I want the code to execute the following operations:
1. Open the first folder (for a year, for instance, 2000)
2. Then open the first folder in this parent folder (monthly folder, for instance, January)
3. open the first Excel file in the folder
4. Then copy the data from the predefined cells
5. Paste the copied data to the destination Excel file
6. Close the Excel file from which the data was copied
7. Save the destination Excel file.
Then, the second Excel file in the first folder needs to be opened, and the steps 4 - 7 to be repeated. After the data from all Excel files in the folder is copied, the code needs to open the second month folder and copy the data from Excels in this folder, and so on.
Once all data for a year is copied, the code moves to another year, until all 22 years data is copied.
Implications:
1. The data is inconsistent in some of the spreadsheets, i.e. the raws numbers can differ; therefore, it will be better to use some function as VLOOKUP to make sure that correct data is copied.
2. There are Excel spreadsheets for certain days, where new data is not written. For instance, in the Excel spreadsheet of 10 May, the test data will be for 9 May (one of the copied cells is date of the test). I would like this data not to be copied. Instead, it will be good to write the date as 10 May and leave the space as blanks in the destination spreadsheet.
I think that this is the essential information for the moment, please let me know in case any other information is needed.
There are several sheets in each Excel file, and I need only certain values from one of the sheets.
I want the code to execute the following operations:
1. Open the first folder (for a year, for instance, 2000)
2. Then open the first folder in this parent folder (monthly folder, for instance, January)
3. open the first Excel file in the folder
4. Then copy the data from the predefined cells
5. Paste the copied data to the destination Excel file
6. Close the Excel file from which the data was copied
7. Save the destination Excel file.
Then, the second Excel file in the first folder needs to be opened, and the steps 4 - 7 to be repeated. After the data from all Excel files in the folder is copied, the code needs to open the second month folder and copy the data from Excels in this folder, and so on.
Once all data for a year is copied, the code moves to another year, until all 22 years data is copied.
Implications:
1. The data is inconsistent in some of the spreadsheets, i.e. the raws numbers can differ; therefore, it will be better to use some function as VLOOKUP to make sure that correct data is copied.
2. There are Excel spreadsheets for certain days, where new data is not written. For instance, in the Excel spreadsheet of 10 May, the test data will be for 9 May (one of the copied cells is date of the test). I would like this data not to be copied. Instead, it will be good to write the date as 10 May and leave the space as blanks in the destination spreadsheet.
I think that this is the essential information for the moment, please let me know in case any other information is needed.