Running Code on Each Sheet in an Excel Workbook
 

The following code will run on the selected worksheet. Is there a way
to make this run on all worksheets in a workbook? The file this is to
run on contains 52 worksheets in 1 workbook.

Thanx!!


Sub clear9s()
Dim counter
counter = 0


If ActiveCell.Value = "" Then Exit Sub


Do Until ActiveCell.Value = ""
If ActiveCell.Value = "-9999" Then
ActiveCell.Value = "not available"
With Selection.Font
.Size = 6
End With
End If
ActiveCell.Offset(1, 0).Select
counter = counter + 1

Loop
ActiveCell.Offset(-counter, 1).Select

clear9s

End Sub

Running Code on Each Sheet in an Excel Workbook
 

try using the

"ActiveWorkbook.Worksheets"

command to run the macro for all sheets

--
Message posted from http://www.ExcelForum.com

Running Code on Each Sheet in an Excel Workbook
 

Sub clear9s()
Dim counter
counter = 0
for each sh in Worksheets
sh.Activate
set rng = ActiveCell
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "-9999" Then
ActiveCell.Value = "not available"
With Selection.Font
.Size = 6
End With
End If
ActiveCell.Offset(1, 0).Select
counter = counter + 1

Loop
rng.select
Next
End Sub

--
Regards,
Tom Ogilvy

"bearie" wrote in message
om...
The following code will run on the selected worksheet. Is there a way
to make this run on all worksheets in a workbook? The file this is to
run on contains 52 worksheets in 1 workbook.

Thanx!!


Sub clear9s()
Dim counter
counter = 0


If ActiveCell.Value = "" Then Exit Sub


Do Until ActiveCell.Value = ""
If ActiveCell.Value = "-9999" Then
ActiveCell.Value = "not available"
With Selection.Font
.Size = 6
End With
End If
ActiveCell.Offset(1, 0).Select
counter = counter + 1

Loop
ActiveCell.Offset(-counter, 1).Select

clear9s

End Sub