Freeze Formulas - Replace Worksheet


G

Gary''s Student

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
 
Ad

Advertisements

M

Mike H

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.
 
M

Mike H

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.
 
G

Gary''s Student

Thanks.

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

Mike H

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.
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top