Hello,
So I have a project that I need to get done quickly. Thought I could do it manually but I have just crashed and burned
I think I need to quickly figure out VBA or Macros.
I need to gather data from more than 300 workbooks each with 31 tabs of data, unfortunately the data is scattered in the tabs so I couldn't use ranges. I went and created a 'collection sheet’ with ~3,800 rows in column A and in column B I 'mapped' the location of the data for example ='Tab 1 data"!P3 etc. The plan was to copy the sheet to be the first tab in each 'target_workbook' then copy the data in column B and paste the values to a workbook called 'rollup.xlsx'. I have just crashed and burned, I hope someone can point me in the right direction,
Can VBA or a Macro to:
1. Unprotect 'target_workbook' in folder source.
2. Add 'collection sheet’ to the workbook from book1.xlsm.
3. Have the 'collection sheet’ work (when I copy to new WB manually I get #REF! in every cell in column B)
3. Copy the values in 'collection sheet' column B1:B3800 to rollup.xlsx next empty column.
4. Remove 'collection sheet’ after data collection
5. Protect WB again
6. Move workbook to 'done' directory
7. Go back to step 1 for next .xlsm till folder is empty.
I this possible or do I need to start over in a new direction?
Where do I start?
Sorry for the long post! I appreciate any help, direction, or ideas!
Regards,
Dave
So I have a project that I need to get done quickly. Thought I could do it manually but I have just crashed and burned
I think I need to quickly figure out VBA or Macros.
I need to gather data from more than 300 workbooks each with 31 tabs of data, unfortunately the data is scattered in the tabs so I couldn't use ranges. I went and created a 'collection sheet’ with ~3,800 rows in column A and in column B I 'mapped' the location of the data for example ='Tab 1 data"!P3 etc. The plan was to copy the sheet to be the first tab in each 'target_workbook' then copy the data in column B and paste the values to a workbook called 'rollup.xlsx'. I have just crashed and burned, I hope someone can point me in the right direction,
Can VBA or a Macro to:
1. Unprotect 'target_workbook' in folder source.
2. Add 'collection sheet’ to the workbook from book1.xlsm.
3. Have the 'collection sheet’ work (when I copy to new WB manually I get #REF! in every cell in column B)
3. Copy the values in 'collection sheet' column B1:B3800 to rollup.xlsx next empty column.
4. Remove 'collection sheet’ after data collection
5. Protect WB again
6. Move workbook to 'done' directory
7. Go back to step 1 for next .xlsm till folder is empty.
I this possible or do I need to start over in a new direction?
Where do I start?
Sorry for the long post! I appreciate any help, direction, or ideas!
Regards,
Dave
