Solved

Copying shhets with formula in Excel

Posted on 2015-01-22
6
90 Views
Last Modified: 2015-04-19
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
0
Comment
Question by:shaunwingin
  • 2
  • 2
6 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40563780
HI,

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
0
 

Author Comment

by:shaunwingin
ID: 40563805
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....
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40563836
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

Open in new window

0
 

Author Comment

by:shaunwingin
ID: 40563870
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
0
 
LVL 3

Accepted Solution

by:
Onisan earned 500 total points
ID: 40720580
I would think that the easiest solution is to have a sheet in your Excel workbook that holds an exact copy of your sheet with formulas and have it Hidden, then you can create a copy of it either by unhiding and copying or by doing so in code. either way it will be a very simple process which means you always have a reliable backup.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
strDist challenge 35 96
Microsoft C++ code failing in executable that worked 9 84
listing all functions in JavaScript 19 176
Image decoding from Camera 3 72
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now