5

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

George Terziev
  • 129
  • 1
  • 3
  • 14

5 Answers5

7

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.

Ralph
  • 9,284
  • 4
  • 32
  • 42
0

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.

TheGuyThatDoesn'tKnowMuch
  • 1,240
  • 1
  • 17
  • 28
  • Thank you for the speedy reply. I do not believe this is a good workaround. I should have clarified my situation. There are about 100 cells that reference "the other sheet". "The other sheet" is also 20+ other sheets. I wanted to see how to do it for 1 and I was going to think of a way to scale it up. Any other suggestions? – George Terziev Jun 13 '16 at 13:58
  • Override the text in the original sheet maybe? Why do you need to create new sheets? – TheGuyThatDoesn'tKnowMuch Jun 13 '16 at 13:59
  • The new sheets contain different data for the scenario I am testing. A bunch of booleans, IDs, and some values. The sheet that pulls all the data has a bunch of lookups and length IF trees and it gets pretty messy if I try to find and replace 20 text values in every cell that has a formula. – George Terziev Jun 13 '16 at 14:02
  • Is there some functionality in 2016 I can toggle that would just preserve the formulas as is? – George Terziev Jun 13 '16 at 14:04
0

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.

Rui Rebelo de Andrade
  • 101
  • 1
  • 8
0

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.
Morris Stemp
  • 1
0

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.

Leigh Holt
  • 1