ActiveWorkBook versus ActiveSheet

  • Thread starter Thread starter Boiler-Todd
  • Start date Start date
B

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
 
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
 
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
 
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
Click to expand...
set rngToSearch = nothing '<-- added
On Error Resume Next
Set rngToSearch = wks.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
Click to expand...

will avoid the bug.
 
Yup... Thanks...
--
HTH...

Jim Thomlinson


Dave Peterson said:
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.
Click to expand...
 
Back
Top