title: Updating a workbook's existing structured table from an external master table?
body: <p><strong>Currently:</strong>  I have a few sheets that contain one or more tables (e.g., Sheet1 has one table while Sheet2 has three tables).  These same sheets are found in several end user workbooks.  The tables in those sheets are used by structured references within cell formulas in the workbook.  </p>
  
  <p><strong>Goal:</strong>  I would like to have another workbook (a master workbook) that only has the sheets that contain the tables.  Updates would be manually made to the master workbook i.e., add a row, delete a row, edit a row, add a column, delete a column, edit a column.  <em>Once updates are completed with the master workbook, the updated [master] sheets would then be placed into the various user workbooks, replacing the existing sheets and thus the tables (using VBA)...without breaking the structured references!  No #REF errors!</em>  Well, this is what I'm envisioning, however, it doesn't have to be exactly this.  Also, the common table sheets must be in the end user workbook, no external referencing!  Unless it's part of the process to make what I need work, of course :-)</p>
  
  <p>I'm very handy with VBA but not a MVP.  Assume all of the workbooks -- both end user and master -- exist in the same directory.  The master workbook would contain the code that would deliver the updated sheets to the end user workbooks.  Excel 2007 &amp; 2010 in Windows 7 environment.</p>
  
  <p>I don't have any code as I'm trying to manually figure it out first without breaking anything.  Suggestions welcomed!  Thank you.</p>
  
  <hr>
  
  <p><strong>[Update 13Jun2012]</strong> Hopefully this more long-winded explaination will help.</p>
  
  <p><strong>What I'm doing:</strong>
  I have a workbook that (essentially) copies itself into an end user workbook (1 to n times based on user form inputs).  The end user workbook is protected to keep end users from making mistakes in certain formulas (various forms of sheet/cell protection).  There are some sheets that contain tables and these tables are the same for all users.  These tables are used for data validation (e.g., Allow: List; Source: =timing_droplist) and VLOOKUPs  (e.g., =VLOOKUP($W8,Timing_table,FE$5+1,FALSE)).  These end user workbooks are created once a year but updated three times during the year (they're for finance/budgeting).  The tables can be updated at any time.  Since data is added to the end user workbooks it's not convienent to re-generate those workbooks with the updated tables and then have the end user re-enter all their data.  So it would better if the existing end user workbooks could have the common tables updated all together (using VBA).</p>
  
  <p><strong>What's happening (just ONE example):</strong>
  "Timing" sheet exists and contains the table "Timing_table".  If the master "Timing" sheet is added to the workbook, Excel renames it to "Timing (2)".  This is normal.  But the table in the added sheet <em>becomes local instead of globle</em> AND it, too, gets renamed, FOR EXAMPLE, "Timing_table12".  I don't know how Excel comes up with the digits for the table name and they could change.  If I delete the old "Timing" sheet all references to it are broken (of course) -- pulling up Name Manager shows #REF! in the value column.  Renaming the new references doesn't fix the issue because the new references are local to the new sheet.</p>
  
  <p>I tried manually cutting and pasting a table into another, pasting as Text, and that doesn't keep formulas (basically it performs a Paste as Values operation) and, overall, doesn't work.  Just looking for options.  External references to these tables is not an option.</p>