Freeze Formulas - Replace Worksheet
 

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

Freeze Formulas - Replace Worksheet
 

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

Freeze Formulas - Replace Worksheet
 

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

Freeze Formulas - Replace Worksheet
 

Thanks.

Are there any approaches if the user can not un-protect the Calcs worksheet?
--
Gary''s Student - gsnu201001


"Mike H" wrote:

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

Freeze Formulas - Replace Worksheet
 

Are there any approaches if the user can not un-protect the Calcs worksheet?

As soon as I say this then someone will (Hopefully) prove me wrong but I
very much doubt it. There are other approaches and the first one I considered
was

Loop through usedrange
if cell hasformula
cell.value="'" and cell.value 'add apostrophe
end if
end loop

do the deletion
create new sheet

loop to remove apostrophes

But this of course requires unprotection

The only other thought that occurs is the owner could be protect the sheet
using 'userinterface only' leaving the sheet protected from the users but
available to VB

HTH
--
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:

Thanks.

Are there any approaches if the user can not un-protect the Calcs worksheet?
--
Gary''s Student - gsnu201001


"Mike H" wrote:

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

Freeze Formulas - Replace Worksheet
 

I found a relatively easy solution. Thanks for your help!
--
Gary''s Student - gsnu201001


"Mike H" wrote:

Are there any approaches if the user can not un-protect the Calcs worksheet?

As soon as I say this then someone will (Hopefully) prove me wrong but I
very much doubt it. There are other approaches and the first one I considered
was

Loop through usedrange
if cell hasformula
cell.value="'" and cell.value 'add apostrophe
end if
end loop

do the deletion
create new sheet

loop to remove apostrophes

But this of course requires unprotection

The only other thought that occurs is the owner could be protect the sheet
using 'userinterface only' leaving the sheet protected from the users but
available to VB

HTH
--
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:

Thanks.

Are there any approaches if the user can not un-protect the Calcs worksheet?
--
Gary''s Student - gsnu201001


"Mike H" wrote:

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