Shaun Wingrin
asked on
Copying shhets with formula in Excel
Say, I've a sheet with formula in Excel which occasionally gets deleted. How can I copy the sheet back from a back up copy and ensure that the formula pertain to the new Excel spread sheet? There is the option of copy sheet, from 1 spread sheet to another, but will this bring the formula in and have them pertain to the new spread sheet? I am looking for an efficient way of carrying out this operation.
Thanks
Shaun
Thanks
Shaun
ASKER
Its not about protection - this sheet is a rough working sheet that gets deleted before xls is sent to client - but don't save xls, Sometimes we save the xls and then need to restore sheet....
with this code in the ThisWorkbook module you could force a SaveAs
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strFileName As String
Cancel = True 'Cancel the save operation
strFileName = Application.GetSaveAsFilename(Replace(ThisWorkbook.Name, ".xls", "_ClientVersion.xls"))
If strFileName <> Me.FullName And strFileName <> "False" Then
Application.EnableEvents = False
ThisWorkbook.SaveAs strFileName
Application.EnableEvents = True
End If
End Sub
ASKER
Pls don't confuse the solution: I'm after this:
"Say, I've a sheet with formula in Excel which occasionally gets deleted. How can I copy the sheet back from a back up copy and ensure that the formula pertain to the new Excel spread sheet? There is the option of copy sheet, from 1 spread sheet to another, but will this bring the formula in and have them pertain to the new spread sheet? I am looking for an efficient way of carrying out this operation.
Thanks
Shaun
"Say, I've a sheet with formula in Excel which occasionally gets deleted. How can I copy the sheet back from a back up copy and ensure that the formula pertain to the new Excel spread sheet? There is the option of copy sheet, from 1 spread sheet to another, but will this bring the formula in and have them pertain to the new spread sheet? I am looking for an efficient way of carrying out this operation.
Thanks
Shaun
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you protect the workbook structure, users cannot insert, delete, rename, move, copy, hide or unhide worksheets anymore.
On the Review tab, click Protect Workbook, make sure Structure is ticked and OK
Regards