Hi guys, am currently having a look around for a solution to my problem, but though i'd ask here first in case someone has already come across this before.
I have multiple workbooks, all in the same format (i.e. worksheet names and data layouts), saved in a single folder. I want to copy data from multiple worksheets in each workbook into a master workbook.
I want to write a macro that will go into the first workbook, go to a worksheet, copy a range of data, go back to the master workbook, paste the data. (This will repeat for a number of worksheets)
Then it will go to the next workbook in the folder, and do the same.
Essentially my work structure is as follows:
1. Open master workbook manually
2. Select folder containing source workbooks (via dialogue box)
3. Run macro
i) workbook1.activate
ii) select first worksheet, copy range of data
iii) masterworkbook.activate
iv) select first worksheet, paste range of data
v) repeat steps i) to iv) for 5 worksheets total
vi) repeat steps i) to v) for n workbooks total
I can write the code that will copy and paste data from one workbook to another.
However, can anyone help with:
1. Cycling through the multiple workbooks - would I need to specifiy workbook names? (this is fine to do as they won't change, but wondering if there is a quicker option)
2. Keeping each active workbook (apart from the master) hidden while the macro is running
Any help much appreciated!
I have multiple workbooks, all in the same format (i.e. worksheet names and data layouts), saved in a single folder. I want to copy data from multiple worksheets in each workbook into a master workbook.
I want to write a macro that will go into the first workbook, go to a worksheet, copy a range of data, go back to the master workbook, paste the data. (This will repeat for a number of worksheets)
Then it will go to the next workbook in the folder, and do the same.
Essentially my work structure is as follows:
1. Open master workbook manually
2. Select folder containing source workbooks (via dialogue box)
3. Run macro
i) workbook1.activate
ii) select first worksheet, copy range of data
iii) masterworkbook.activate
iv) select first worksheet, paste range of data
v) repeat steps i) to iv) for 5 worksheets total
vi) repeat steps i) to v) for n workbooks total
I can write the code that will copy and paste data from one workbook to another.
However, can anyone help with:
1. Cycling through the multiple workbooks - would I need to specifiy workbook names? (this is fine to do as they won't change, but wondering if there is a quicker option)
2. Keeping each active workbook (apart from the master) hidden while the macro is running
Any help much appreciated!
