Can this be easily done in VBA?

dcampana

New Member
Joined
Apr 3, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top