ActiveWorkBook versus ActiveSheet

[Boiler-Todd]

I have a macro that I would like to update every tab in the workbook, but I
can only get the one active sheet to update when I run the macro. I tried to
use two "FOR" staements but got an error. I want this macro to update every
sheet every time the workbook is opened. Here is my code. Any help would be
great as I rarely write macros so I am doing this by trial and error and
reading past posts. Thanks!

Sub Workbook_Open()
' This Macro will clear out the UNLOCKED cells of a sheet that is older
' than todays date by looking at the date cell in the spreadsheet
Dim cell As Range
With ActiveWorkbook
If Date > Range("A1").Value Then
For Each cell In ActiveSheet.UsedRange
If Not cell.Locked Then cell.ClearContents
Next cell
End If
End With
End Sub

 

[Per Jessen]

Hi
You have to iterate over the sheets in ActiveWorkbook:

Sub Workbook_Open()
' This Macro will clear out the UNLOCKED cells of a sheet that is older
' than todays date by looking at the date cell in the spreadsheet
Dim cell As Range
For Each sh In ActiveWorkbook.Sheets
If Date > sh.Range("A1").Value Then
For Each cell In sh.UsedRange
If Not cell.Locked Then cell.ClearContents
Next cell
End If
Next
End Sub

Regards,
Per

 

[Jim Thomlinson]

Untested but this should be close. It should be pasted in the ThisWorkbook
module.

Private Sub Workbook_Open()
Dim wks As Worksheet
Dim rngToSearch
Dim rng As Range

For Each wks In ThisWorkbook.Worksheets
If wks.Range("A1").Value < Date Then
On Error Resume Next
Set rngToSearch = wks.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rngToSearch Is Nothing Then
For Each rng In rngToSearch
If rng.Locked = False Then rng.ClearContents
Next rng
End If
End If
Next wks
End Sub

 

[Dave Peterson]

There's a small bug in your code.

If that rngtosearch is found (not nothing) on one worksheet, but there are no
constants on the next, then rngtosearch won't change to nothing. It'll still be
pointing back at the previous worksheet.

Since the code is not doing anything really damaging, it'll just loop through
that other range again and clear the contents again.

Changing the loop to be:

If wks.Range("A1").Value < Date Then

set rngToSearch = nothing '<-- added

On Error Resume Next
Set rngToSearch = wks.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

will avoid the bug.

 

[Jim Thomlinson]

Yup... Thanks...
--
HTH...

Jim Thomlinson

There's a small bug in your code.

If that rngtosearch is found (not nothing) on one worksheet, but there are no
constants on the next, then rngtosearch won't change to nothing. It'll still be
pointing back at the previous worksheet.

Since the code is not doing anything really damaging, it'll just loop through
that other range again and clear the contents again.

Changing the loop to be:

set rngToSearch = nothing '<-- added

will avoid the bug.

 

[Boiler-Todd]

Thanks everyone for your help. I will give it a try.