How do I keep Cell Referencing in Excel if I replace sheet?

Question

I have a sheet with a bunch of data. I then have a different sheet that references multiple cells in that first sheet. If I want to delete the first sheet and replace it with an identical sheet (identical in every sense, ie sheet name, data type in each cell, format, etc, except for the actual text data in each cell), the references in the other sheet are lost, and all my cells produce a #REF! error.

Is there any way of preserving the references and replacing or overwriting the sheet, without having to manually cut and paste the information?

Thank in advance,

George

Answer 1

Had the same problem today, as I have quite a messy excel file which I have to use to get my data out. First I want to copy the original sheet to my own excel file, as I am not the owner - but data processing becomes really slow if it's an external file. So copy once per day takes 20 seconds and everything else becomes fast.

To automate this I use the following code with the aforementioned workaround:

Sub CopySheetMacro()
    ' Define the name of the cell to store the last run date
    Dim LastRunCell As Range
    Set LastRunCell = ThisWorkbook.Sheets("Sheet1").Range("A1") ' Change the sheet and cell as needed

    ' Check if the macro was already run today
    If LastRunCell.Value <> Date Then
        Dim SourceWorkbook As Workbook
        Dim DestWorkbook As Workbook
        Dim SourceSheet As Worksheet
        Dim DestSheet As Worksheet
        Dim SheetName As String

        ' Define the source workbook path (change as needed)
        Dim SourceWorkbookPath As String
        SourceWorkbookPath = "D:\tabelle 2023.xlsx"

        ' Define the source sheet name
        Dim SourceSheetName As String
        SourceSheetName = "EG" ' Change this to the actual sheet name

        ' Define the destination sheet name
        Dim DestSheetName As String
        DestSheetName = "EG" ' Change this to the desired name

        ' Replace "=" with "#$%" in the "Übersicht" sheet
        Dim sht As Worksheet
        Set sht = ThisWorkbook.Sheets("Übersicht")
        sht.Cells.Replace What:="=", Replacement:="#$%", LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

        ' Delete sheet with identical name
        On Error Resume Next ' Ignore errors if the sheet doesn't exist
        Application.DisplayAlerts = False ' Turn off alerts
        ThisWorkbook.Sheets(DestSheetName).Delete
        Application.DisplayAlerts = True ' Turn alerts back on
        On Error GoTo 0 ' Reset error handling

        ' Open the source workbook with read-only access
        Set SourceWorkbook = Workbooks.Open(SourceWorkbookPath, ReadOnly:=True)

        ' Set references to the source and destination sheets
        Set SourceSheet = SourceWorkbook.Sheets(SourceSheetName)
        Set DestWorkbook = ThisWorkbook ' The destination workbook is the one where the macro is running

        ' Copy the source sheet to the destination workbook (without adding a new sheet)
        SourceSheet.Copy Before:=DestWorkbook.Sheets(1) ' Copy before the first sheet in the destination workbook

        ' Close the source workbook without saving changes
        SourceWorkbook.Close SaveChanges:=False

        ' Update the last run date in the cell
        LastRunCell.Value = Date
    End If
    
    ' Restore "=" in the "Übersicht" sheet
    Set sht = ThisWorkbook.Sheets("Übersicht") ' Re-define sht
    sht.Cells.Replace What:="#$%", Replacement:="=", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Answer 2

The most simplest way I have found to do this is:

In my example, I have a summary tab that has all the formulas referencing each tab of my workbook so I have can have a one place to look for how many parts i have ordered for each site. Each site has a tab which its contains the total amount of products for that site. I get an updated workbook with all of the sites and product counts from the supplier each month. I import a copy of my summary tab from my last report (which has all of the formulas referencing each of these tabs) and then insert it into the new updated workbook from the supplier. It will still reference to my old workbook for the data. To update that: I go to Data Tab, then Edit Links, select the old worksheet/workbook its currently referencing, then select Change Source, then Locate the new file (which is the file I'm currently in trying to update. It will then auto update every reference and remove the old path. It will now only reference the tab name of the current document. Hopefully I explained it good enough. Hope it helps.

Answer 3

To restate the problem:

[Sheet1] contains the raw data
[Sheet2] has references to the raw data on [Sheet1] in the form of '[Sheet1]'![Cell Reference]
You wish to update all the raw data by replacing the old [Sheet1] with a new [Sheet1].
When you delete the old [Sheet1], all the formula references on [Sheet2] change to #REF.

My Workaround

I use Indirect Referencing to [Sheet1].
Let's assume my [Sheet1] is called "RawData".
My formulas in [Sheet2] will indirectly reference [Sheet1] as follows:
Indirect("RawData!A1")
The **big downside** to this is that if cell A1 on the [RawData] sheet is moved around, the A1 will not move.  **This is NOT good.**
I almost always use Tables so I am referencing table names, not specific cells. And this method works great for that.

Answer 4

After reading all the comments, my advice would be the following approach (warning, it is a work around, not a clean solution):

Sub DeleteSheets_KeepingReferences()
     Dim sht As Worksheet
     Set sht = ThisWorkbook.Sheets("")

     sht.Cells.Replace What:="=", Replacement:="#$%", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

     'Delete sheet
     Application.DisplayAlerts = False
     wb.Sheets(sht).Delete

     'Code to copy sheet

     sht.Cells.Replace What:="#$%", Replacement:="=", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Basically, instead of replacing the sheet name in the cells (as proposed already by @TheGuyThatDoesn'tKnowMuch), I'm replacing the equal sign, temporarily converting the cells into text, and in the end I convert the equal sign again, creating formulas in all cells formulas again, as pretended.

Answer 5

Here is a solution I like to work with:

1. Before deleting the old sheet right-click on the sheet name and move (not copy) the sheet you wish to delete to a new / empty workbook.
2. Now, all links within the original file are automatically converted to reference the newly created workbook and all these links show up in the Data tab (Excel menu) Connections ► Edit Links.
3. Now you can insert the new sheet you wish to reference and in the above menu you can change the link to reference the original file. So, you are essentially changing the reference to itself back again (thus removing the reference to the newly created external workbook).

Note, that in this solution the replacement sheet will have to have the same name when inserted. Yet, you can (of course) change the sheet name after the above process is completed.

I surely hope I explained it sufficiently. Yet, don't hesitate to let me know if you require additional explanations.

Answer 6

Create the new sheet, then do a find & replace on the sheet with formulas to find the original sheet name and replace with the new sheet name. Then delete the old sheet, and rename the new sheet to whatever you want.