Just noticed the 'protected bit. I'm sure you know how to do this but for
completeness

Sub Kill_Ref()
Application.DisplayAlerts = False
Sheets("Data").Delete
Application.DisplayAlerts = True
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "Data"
With Sheets("Calcs")
..Unprotect
..Cells.Replace What:="#REF", Replacement:="DATA", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
..Protect
End With
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Maybe this

Sub Kill_Ref()
Sheets("Data").Delete
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "Data"
With Sheets("Calcs")
.Cells.Replace What:="#REF", Replacement:="DATA", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Gary''s Student" wrote:

I am trying to help someone with a relatively simple problem. They have a
workbook with two worksheets: Calcs and Data

The Calcs worksheet contains protected formulas that refer to the Data
worksheet values. The user wants to be able to delete the Data Tab, insert a
new tab and re-name the new tab Data. For example, if a cell in Calcs
contains:

=Data!A1

then after the tabs are adjusted the result should still be:

=Data!A1

ANY solution,manual or VBA, is acceptable.

The formulas in Calcs need to be unchanged
--
Gary''s Student - gsnu201001