ActiveWorkBook versus ActiveSheet
 

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

ActiveWorkBook versus ActiveSheet
 

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

"Boiler-Todd" skrev i meddelelsen
...
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

ActiveWorkBook versus ActiveSheet
 

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
--
HTH...

Jim Thomlinson


"Boiler-Todd" wrote:

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

ActiveWorkBook versus ActiveSheet
 

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 wrote:

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
--
HTH...

Jim Thomlinson

"Boiler-Todd" wrote:

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


--

Dave Peterson

ActiveWorkBook versus ActiveSheet
 

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

Jim Thomlinson


"Dave Peterson" wrote:

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 wrote:

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
--
HTH...

Jim Thomlinson

"Boiler-Todd" wrote:

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


--

Dave Peterson

ActiveWorkBook versus ActiveSheet
 

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

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Dave Peterson" wrote:

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 wrote:

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
--
HTH...

Jim Thomlinson

"Boiler-Todd" wrote:

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


--

Dave Peterson