Deleting worksheet breaks my formulas

[Guest]

I have a spreadsheet with a number of tabs. The first tab is my "totals"
where there are formulas to total certain cells in each of the other tabs.

Each week I send the workbook out to a number of individuals asking them to
update the tab with their name. When I receive their replies, I delete their
old tab and copy in their new tab.

This breaks my formulas in the totals tab, even though the copied in
sheets's tabs have the same names.

Is there any way I can keep excel from tracking whether or not the tabs are
there until I am ready to calculate the totals? I already set them to manual
calculation.

Any help would be greatly appreciated.

 

[Bernie Deitrick]

Before you delete the old sheet and copy in the new sheet, select your "totals" sheet and run this
macro:

Sub SAFormulaToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
myCell.Formula = "'" & myCell.Formula
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub

Then remove the old sheet, copy in the new sheet, and then select all the cells that should have
formulas on your "Total" sheet and run this macro:

Sub SATextToFormula()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub


HTH,
Bernie
MS Excel MVP

 

[Guest]

as long as it is exactly the same name, try
the indirect function
=indirect("'sheet name'!cell")

 

[Dave Peterson]

Instead of deleting the old worksheet, why not just copy the cells and paste
them into the worksheet that has that same name.