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

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"ToddEZ" wrote in message
...
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

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


"ToddEZ" wrote in message
...
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

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


--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"ToddEZ" wrote in message
...
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

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

--
Regards,
Bill Renaud

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.


--


Gary


"Bill Renaud" wrote in message
...
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

--
Regards,
Bill Renaud

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

"Bill Renaud" wrote:

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

--
Regards,
Bill Renaud

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.

On Nov 10, 4:20 pm, ToddEZ wrote:
Works GREAT!!!!!!! This saves me so much time! Thanks!!!



"Bill Renaud" wrote:
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

--
Regards,
Bill Renaud- Hide quoted text -

- Show quoted text -