Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I freeze individual cells with formulas in them | Excel Worksheet Functions | |||
How do I freeze formulas when cutting and pasting other cells? | Excel Worksheet Functions | |||
Freeze worksheet? | Excel Worksheet Functions | |||
Replace all formulas in one worksheet to link to different worksh | Excel Discussion (Misc queries) | |||
How can I freeze formulas in Excel? | Excel Worksheet Functions |