auto archiving excel doc

austin350s10

Active Member
Joined
Jul 30, 2010
Messages
321
I am working on a multi-sheet workbook that captures data over a one year span. After the new year I would like a script that backs-up the current workbook, saving it with a specific file format, something like:
"string cell reference"," ","old year".

The tricky part is when it is finished backing-up the workbook I would like the script to also clear out large chunks of data in most of the non-protected cells including many checkboxes. This is so the user can begin to re-enter data for the new year without loosing important data like names and phone numbers...est.

Lastly, when it is finished clearing out the data it should automaticly save the workbook again with a specific file format something like:
"string cell reference"," ","new year".

How can this be done??

The only part I know how to do write a script that runs when the new year is detected just don't know the back-up and clearing of contents portion:

Code:
[B][B]Sub test()

Dim CurrentYear As Integer
Dim NextYear As Integer

CurrentYear = Application.WorksheetFunction.Text(Date, "yyyy")
[/B]NextYear = Range("D6").Value  'next year is in D6 the first time "Lets Say 2011"[/B]
[B][B]
If CurrentYear =  NextYear Then
MsgBox ("The macro has worked")

[/B]Range("D6").Value = NextYear + 1 'Should change the original value of 2011 to 2012[/B][B][B]

End If

End Sub[/B][/B]</pre>
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,746
Members
418,149
Latest member
amamiche67

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top