multiple sheets

[Guest]

Hello,
How do I make the following macro run for all sheets in my workbook? I
tried selecting all sheets, but it dosen't work. THanks.


Sub frmt()
Dim cell As Range
Application.ScreenUpdating = False


With ActiveSheet.UsedRange
ActiveSheet.Name = Range("e61").Value
End With

With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns("h").SpecialCells(xlCellTypeFormulas)
If cell.Text = "dlt" Then cell.EntireRow.Hidden = True
Next cell
End With
End Sub

 

[Bernard Liengme]

I modified your sub to name each sheet but I has to comment out the second
part - it gave errors. I will leave that part to you
Sub frmt()
Dim cell As Range
Dim ws As Worksheet
Application.ScreenUpdating = False

For Each ws In Worksheets

ws.Name = ws.Range("e61").Value

' With ws.UsedRange
' .Rows.Hidden = False
' For Each cell In .Columns("h").SpecialCells(xlCellTypeFormulas)
' If cell.Text = "dlt" Then cell.EntireRow.Hidden = True
' Next cell
' End With
Next ws
End Sub

best wsihes

 

[Gary Keramidas]

give this a try:

Sub frmt()
Dim cell As Range
Dim ws As Worksheet
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
With ws
.Name = .Range("e61").Value
With .UsedRange
.Rows.Hidden = False
On Error Resume Next
For Each cell In
..Columns("h").SpecialCells(xlCellTypeFormulas)
If cell.Text = "dlt" Then cell.EntireRow.Hidden =
True
Next
On Error GoTo 0
End With
End With
Next
Application.ScreenUpdating = True
End Sub

 

[Gary Keramidas]

didn't wrap very well, hopefully this is better. you can indent the lines
manually or with smart indenter.

Sub frmt()
Dim cell As Range
Dim ws As Worksheet
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
With ws
.Name = .Range("e61").Value
With .UsedRange
.Rows.Hidden = False
On Error Resume Next
For Each cell In .Columns("h").SpecialCells(xlCellTypeFormulas)
If cell.Text = "dlt" Then cell.EntireRow.Hidden = True
Next
On Error GoTo 0
End With
End With
Next
End Sub

 

[Bill Renaud]

Try setting up your code to be more like the following. (This is not
totally tested. There are still some errors in your For loop that I can't
debug without knowing your data better. Also, be careful. The SpecialCells
property may fail, if there are no formulas!)

'----------------------------------------------------------------------
Public Sub FormatAllWorksheets()
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets
FormatWorksheet ws
Next ws
End Sub

'----------------------------------------------------------------------
Sub FormatWorksheet(ws As Worksheet)
Dim cell As Range

With ws
.Name = .Range("e61").Value
End With

With ws.UsedRange
.Rows.Hidden = False
For Each cell In .Columns("h").SpecialCells(xlCellTypeFormulas)
If cell.Text = "dlt" Then cell.EntireRow.Hidden = True
Next cell
End With
End Sub

 

[Gary Keramidas]

i believe you're seeing the debug error when there are no cells to satisfy the
..SpecialCells(xlCellTypeFormulas) condition. that's why i entered the on error
resume next statement in my code.

 

[Guest]

Works GREAT!!!!!!! This saves me so much time! Thanks!!!

 

[Carl Hartness]

Something to keep in mind: If you have swapped the order of some of
the sheet tabs, and want the order of execution to be the order of the
tabs, use:

Dim x%
for x% = 1 to worksheets.count
sheets(x%).Name = sheets(x%).Range("e61").Value
next x%

Carl.