Switch Worksheet Names without Updating References to those Sheets

Question

My file has two identical Worksheets for users to input two different sets of assumption variables, called "InputA" and "InputB". I want to quickly switch which Input sheet is feeding into the other sheets of the model.

Using Find and Replace took over 5 minutes, and there were over 350,000 references to "InputA".

I tried the following macro, which takes an instant to run, but unfortunately also changes all references in the workbook, effectively keeping everything referenced to the original input sheet.

Sheets("InputA").Name = "temp"
Sheets("InputB").Name = "InputA"
Sheets("temp").Name = "InputB"

Is there a way to execute the macro but prevent any references to worksheets from changing to the new sheet name, basically freezing everything except the sheet name change? Or perhaps any other solution that will work quickly? I don't want to go through 350,000 instances and rewrite using INDIRECT(), as that is the only other solution I've seen, because my references are complex and nested and that will take an age.

Thanks.

Answer 1

Assuming that your 2 Input-Sheets have the same structure, I would suggest the following:

1. Create a named range on Workbook-level, name it for example InputData. This range should contain all data from InputA.

2. Create a helper-sheet and name it Input - you can later set it to hidden.

3. Mark the range in the new sheet that is exactly the size of the Input-Data-Range and enter the formula =InputData as Array-formula. You can do so by entering Ctrl+Shift+Enter. The formula should have curly brackets and the sheet should now display the data of InputA.

4. Change all you formulas to refer to the helper Sheet Input instead of InputA.

5. Create a macro:

   Sub switchInput()
   
       Const sheetAName = "InputA"
       Const sheetBName = "InputB"
   
       With ThisWorkbook.Names("inputData")
           If InStr(.RefersTo, sheetAName) > 0 Then
               .RefersTo = Replace(.RefersTo, sheetAName, sheetBName)
           Else
               .RefersTo = Replace(.RefersTo, sheetBName, sheetAName)
           End If
       End With
   End Sub
   

This routine will just change the definition of the named range to point either to the first or second input sheet. As a consequence, the helper sheet will show the data of the selected Input-Sheet. All your formulas itself stays unchanged.