Updating Multiple Tables and Adding New Tables using a Reference Sheet from Master Workbook


New Member
Sep 6, 2018
Workbook A (WB_A)is used by multiple people saved to their local computer. Saved in the same folder is another workbook (WB_B) that is used as a reference. WB_A pulls information from WB_B and populates multiple tables with rows. I need to be able to add new Tables to WB_B so that WB_A can pull the new tables into its reference worksheet that helps populate dropdowns. everything works fine except the updating of tables, and adding new tables to WB_A that were created in WB_B.

Worksheet_C in WB_B has a bunch of similar tables named similar to this:


If I add a new table to Worksheet_3 in WB_B i would continue the naming convention and it would be similar to the first two tables, but the "***" can vary.

Worksheet_1 in WB_A references all the tables on Worksheet_3 within the same workbook. This helps with dropdowns and other GUI setup within the workbook.

the tables on Worksheet_3 should all be named like: Table_***_AA and when the file opens it should make sure all the tables are there and are updated with the information from WB_B.

this may be an easy way to visualize this:

Table Location -----Process----> Table Location

WorkbookB Worksheet3 ----process------> WorkbookA - Worksheet3

TableToTransfer_123456_AA -----Update-----> Table_123456_AA
TableToTransfer_12321_AA ------Update----> Table_12321_AA
TableToTransfer_123789_AA ---Add new table and rename--> Table_123789_AA

Can someone help with the VBA for this macro? please!

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics