Macro to select all sheets

[belvy123]

Hi All

I am needing help with code for a macro
I want to have this macro run on every opening of file and compare to the
current date. If the date is matched or past the current date I want to
select all sheets in the work book and then clear all data in the sheets. And
then save the file also.
Can anyone help with this

Thanks

 

[Gary''s Student]

This workbook event macro assumes the reference date in is cell A1 in the
first sheet. If you open the workbook and the reference date is in the
future, nothing happens. If you open the workbook and the reference date is
in the past, then all the cell in the sheets are cleared and the workbook is
saved:

Private Sub Workbook_Open()
Sheets(1).Activate
If Date > Range("A1").Value Then
For Each sh In Sheets
sh.Cells.Clear
Next
ActiveWorkbook.Save
End If
End Sub


WARNING: mis-using this macro can be as bad as crossing the streams.

 

[belvy123]

Hi
The script works
However I must not be putting it in the right spot
If I put it in say module #1 then it does nothing. If I put it inside a
sheet heading then it runs only when i execute it.
What Am I doing wrong
Thanks

 

[Gary''s Student]

Because it is workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (workbook code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[belvy123]

Works like a charm thanks

anyway to have it remove the code before save???

 

[Gary''s Student]

I don't know how to do it.

 

[belvy123]

Hi Again
I am having one issue that i did not mention before
I have protection on all sheets to keep people from deleting formulas.
what do i need to add to this code to unprotect the sheets as the code trys
to clear the cells

Thanks

 

[Gord Dibben]

Macro starts

ActiveSheet.Unprotect Password:="justme"

Macro does its stuff

ActiveSheet Protect Password:="justme"

End Sub