Table Reference in Formula Changes When Worksheets Copied
<p>I have two sheets in &quot;Property&quot; Workbook that I am copying to &quot;UQP&quot; Workbook.</p>
<blockquote>
<p><strong>PropertyCoverage</strong> - Houses formula <code>=VLOOKUP(&quot;PF&quot;,tPremium,2,0)</code> among other text and simple SUM formula.</p>
<p><strong>xmlProperty</strong> - Houses table <code>tPremium</code> (among other tables; all tables mapped to XML Source)</p>
</blockquote>
<p>When I copy these two sheets (simultaneously) into the UQP workbook the <code>tPremium</code> reference in the formula in the PropertyCoverage sheet automatically changes to <code>tAutoForms</code>, which is another table in the UQP workbook (that has nothing to do with the formula). UQP workbook has several other sheets and tables.</p>
<p>I don't know why this changes automatically, but I think it has something to do with the fact that Excel cannot copy multiples sheets when at least one sheet contains a table.</p>
<p>I have tried the solution below (referenced from <a href="https://blogs.office.com/2009/08/31/copying-worksheets-with-a-list-or-table/" rel="nofollow noreferrer">here</a>). However, the same issue happens. The issue does not happen if I copy to a new workbook. ... <em>Bear with ActiveWorkbook, etc, I only used this as test to see if it would work</em></p>
<pre><code>Sub Copy_Worksheets()

    Dim wbMaster As Workbook
    Set wbMaster = Workbooks(&quot;UniversalQuoteProposal.xlsb&quot;)
    Dim TheActiveWindow As Window
    Dim TempWindow As Window
    With ActiveWorkbook
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
        .Sheets(Array(&quot;PropertyCoverage&quot;, &quot;xmlProperty&quot;)).Copy Before:=wbMaster.Worksheets(1)
    End With
    TempWindow.Close

End Sub
</code></pre>
<p>Here is my original copy code:</p>
<pre><code>Sub CopySheetsToMaster()

Dim wbMaster As Workbook
Set wbMaster = Workbooks(&quot;UniversalQuoteProposal.xlsb&quot;)

Dim sSheetName As String
sSheetName = Replace(ThisWorkbook.Name, &quot;.xlsb&quot;, &quot;&quot;)

Dim sSheets As Variant
sSheets = Array(sSheetName &amp; &quot;Coverage&quot;, &quot;xml&quot; &amp; sSheetName)

'*** =&gt; This is where tPremium gets automatically changed to tAutoForms
ThisWorkbook.Worksheets(sSheets).Copy Before:=wbMaster.Worksheets(1)

wbMaster.ChangeLink ThisWorkbook.FullName, wbMaster.FullName, xlLinkTypeExcelLinks

End Sub
</code></pre>
<p>Lastly, I can work around this by doing a Replace after the sheets are copied, but since this will scale out to a need to bring a possible 7-10 other workbooks with potential formula references to tables and such, I would rather find a cleaner solution if it exists.</p>
<p>Lastly, I am using Excel 2010.</p>