How to Reset a Spreadsheet with a macro

**Alexander_Read** · 09-23-2008, 05:22 AM

Hi

As mentioned I want to create a button that can reset this/a spreadsheet.

DWOR_2.zip

I have locked all the cells and unlocked the cells with the light green background. I.e. where a user can input data.

I haven't added a password yet nor protected the sheet. (the macro needs to work when the sheet is protected.)

I am looking for a macro that can be used to reset the sheet to its original state. However, the sheet needs to be protected.

Any ideas how to do this?

Regards

Alex

**royUK** · 09-23-2008, 05:46 AM

What do you mean by reset? Is this part of an earlier question?

**Alexander_Read** · 09-23-2008, 08:08 AM

Hi

What I mean by reset is that the sheet has certain info on it. When someone changes the info I need a feature that can "reset" the sheet to its original state. i.e the state that you currently see the DWOR is in.

REgards

Alex

**royUK** · 09-23-2008, 09:30 AM

Do you want to clear all unlocked cells?

**Alexander_Read** · 09-23-2008, 10:36 AM

Hi

I don't want to clear the cells, but reset the cells to the original state and formula/numbers that were in them.

**royUK** · 09-23-2008, 10:51 AM

I think the only way would be to make a temporary copy of the sheet, then afterwards delete the change sheet 7 then use the copy. Usually you allow users access to data cells only, not formulas

**Alexander_Read** · 09-25-2008, 04:42 AM

Hi Roy

I agree with you.

I want to make the copy "veryhidden" with properties.

When I do this, the macro can't find it.

How do I get around this? Would you have the specific code I could use?

Regards

Alex

**rwgrietveld** · 09-25-2008, 04:55 AM

Why make it hidden as you are presenting the user with that data anyway. Just make it protected. In your macro you can unprotect/protect with the following code:

  Set Ws = Worksheets("YourSheet")
  Ws.Unprotect
    'your code
  Ws.Protect

**Alexander_Read** · 09-25-2008, 05:03 AM

Hi

If I don't hide the sheets they will confuse the people who are using the excel file. (Many of them are first time excel users).

How do I get the "copy" sheets to be hidden?

Any idea what code I can use if I set the sheets to "veryhidden"?

Thanks for the code that will sort out my protection/unprotection problem, all that is left now is the code to find the hidden sheets and use them.

Regards

Alex

**royUK** · 09-25-2008, 05:37 AM

Thisa code will create a backup sheet & hide it. You can write similar code to delete the amended sheet & unhide the copy, rename it. The visible status for visible is 1

Option Explicit

Sub makeBackup()
    Dim aWs    As Worksheet
    Set aWs = ActiveSheet
    On Error Resume Next
    aWs.Copy after:=ThisWorkbook.Worksheets(Worksheets.Count)
    With ActiveSheet
        .Name = "Temp" & Format(Date, "ddmm")
        .Visible = 2
    End With
    aWs.Select
    On Error GoTo 0
End Sub

**Alexander_Read** · 09-25-2008, 06:39 AM

Hi Roy

If I create a backup sheet and call it "DWOR Copy 1" and then set the properties to "veryhidden", what code could I use for that? Would the above work?

I need to create a master copy of my sheets, and then hide this and use this copy when resetting my sheets.

I am slowly learning more about VBA code and am going on a course shortly, but have a large spreadsheet that I need to complete before the weekend, and my skills are a little limited at the moment. Hence I need the help with the exact code.

I think that the code you wrote will make a backup sheet and hide it. I am looking for the code that will take a backup sheet and use it to overwrite the current sheet. How do I do this?

Many Thanks for your patience and all your help

Alex

**royUK** · 09-25-2008, 07:08 AM

I think this is what you want

  Option Explicit

Sub useTemplate()
    Dim aWs    As Worksheet
    Dim tempWs As Worksheet
    Set tempWs = Worksheets("DWOR Copy 1")
    Dim sWs    As String
    Set aWs = ActiveSheet
    sWs = aWs.Name
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False    'prevent warning about deleting
        On Error Resume Next
        With tempWs
            .Visible = 1
            .Copy after:=ThisWorkbook.Worksheets(Worksheets.Count)
            .Visible = 2
        End With
        aWs.Delete
        With ActiveSheet
            .Name = sWs
        End With
        On Error GoTo 0
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

**Alexander_Read** · 09-25-2008, 08:51 AM

Hi Roy

Just to check, I can create a macro button with this code on a sheet called "DWOR" and it will reset the sheet to its original state (from the "veryhidden" sheet called "DWOR Copy 1")?

Regards

Alex

**Alexander_Read** · 09-25-2008, 11:52 AM

Hi Roy

I have tested it and it works well! :-)

Four things before I can finish it:
1) Will it work with multiple sheets if I duplicate the macro and change the name of the temp worksheets?
2) When the macro is finished running how do I get it to stop highlighting all cells and return to cell A1?
3) It puts the sheet at the end of the sheets, how can I get it to put it where it was? (The order of the sheets is important)
4) I have a different sheet that gets data from the sheet, but after I run the macro, although the sheet is reset, formulae on the other sheet return "#REF!". How do I fix this?

These are all small things, but a little above my current knowledge.

Your help is most appreciated. (I'm almost there and will hopefully get this done by the weekend!)

Alex

**royUK** · 09-25-2008, 12:10 PM

It might be better to copy from the template sheet & paste over the master sheet. The positioning shouldn't be a problem.

I'm about to set off home so if you can attach a small example with the sheet that gets the #REF errors I'll have a look later.

**Alexander_Read** · 09-25-2008, 01:08 PM

Hi

Here is an example of the sheet I want to reset.

I haven't protected the cells yet or locked the workbook. I want to reset both "Monthly Roster" and "week 1".

Reset Examples.zip

REgards

Alex

**Alexander_Read** · 09-26-2008, 04:48 AM

Hi Roy

There was a problem with the attachment I uploaded yesterday.

In this file you will see the two reset buttons I have created.

I want each button to reset the respective sheets.

Here is an example of the sheets I want to reset:
Reset Examples.zip

Many Thanks

Alex

**royUK** · 09-26-2008, 05:23 AM

Is Monthly Roster the sheet to be hidden & used as a template?

**Alexander_Read** · 09-26-2008, 06:02 AM

Hi Roy

You will need to make a copy of the two sheets and then in the VBA code set the properties of the two copied template sheets to "very hidden."

Finally, before completing it, you will need to lock some day entry cells and protect the workbook. The macro should work after all this is done.

Thanks for all your patience with this, it is really appreciated.

Alex

**royUK** · 09-26-2008, 06:19 AM

I can see that you want two hidden sheets based on these two to act as templates. I on't know what cells to lock.

It seems to be a very complicated method, why can't you have a template workbook and create a new one from that each month?

**Alexander_Read** · 09-26-2008, 07:17 AM

Cells to lock, everything except for E5:N39.

With your second point, do you mean that I should just resave the file with a different name every month and keep an original? That will also work.

How to Reset a Spreadsheet with a macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to Reset a Spreadsheet with a macro

re: How to Reset a Spreadsheet with a macro

Resetting a Spreadsheet

Resetting a Spreadsheet

Resetting a Spreadsheet

Thread Information

Users Browsing this Thread

Similar Threads

Macro to create new week spreadsheet

Deleting Query Tables with a Macro

not having a macro open the worksheet in which it was written

How might one "save" a PSWD in a macro temporarily.

Need help: Simple spreadsheet "broke"

Bookmarks

Bookmarks

Posting Permissions