VBA to remove 0 values on multiple sheets

[rrossi]

How do I edit the programming below to perform same process for different worksheets by using one macro?

Sub Format_report()
'
' Format_report Macro
'

ActiveWorkbook.RefreshAll
ActiveSheet.AutoFilter.ApplyFilter

Dim c As Range

For Each c In Range("U1:AD1").Cells
If c.Value = "Hide" Then
c.EntireColumn.Hidden = True

End If
Next c

Dim cell As Range
For Each cell In Range("c7:c532")
If cell <> "" Then
If cell = 0 Then cell.EntireRow.Hidden = True

End If
Next cell

Sheets("Sheet1").Select
Range("C1").Select

MsgBox "Formatting Completed! Report Ready!"

End Sub

 

[DanteAmor]

Hi & welcome to MrExcel.

On all the sheets of the book, or just some?

For all sheets:

Sub Format_report()
'
' Format_report Macro
'
  Dim sh As Worksheet
  Dim c As Range
  
  Application.ScreenUpdating = False
  ActiveWorkbook.RefreshAll
  ActiveSheet.AutoFilter.ApplyFilter
  
  For Each sh In Sheets
    For Each c In sh.Range("U1:AD1").Cells
      If c.Value = "Hide" Then
        c.EntireColumn.Hidden = True
      End If
    Next c
    For Each c In sh.Range("c7:c532")
      If c <> "" Then
        If c = 0 Then c.EntireRow.Hidden = True
      End If
    Next c
  Next sh
  Sheets("Sheet1").Select
  Range("C1").Select
  MsgBox "Formatting Completed! Report Ready!"
End Sub

 

[rrossi]

Just some. There will be 5 sheets. I was expecting to have to list the sheets names. I think that I would need to activate each sheet, perform the functions, and deactivate the sheet so they are not grouped.

 

[rrossi]

Just some. There will be 5 sheets. I was expecting to have to list the sheets names. I think that I would need to activate each sheet, perform the functions, and deactivate the sheet so they are not grouped.

And thank you so much for your help!

 

[DanteAmor]

Just some

Try this:

Sub Format_report()
'
' Format_report Macro
'
  Dim sh As Worksheet
  Dim c As Range
  
  Application.ScreenUpdating = False
  ActiveWorkbook.RefreshAll
  ActiveSheet.AutoFilter.ApplyFilter
  
  For Each sh In Sheets
    Select Case sh.Name
      Case "Sheet1", "Sheet2", "Sheet3", "etc"
        For Each c In sh.Range("U1:AD1").Cells
          If c.Value = "Hide" Then
            c.EntireColumn.Hidden = True
          End If
        Next c
        For Each c In sh.Range("c7:c532")
          If c <> "" Then
            If c = 0 Then c.EntireRow.Hidden = True
          End If
        Next c
    End Select
  Next sh
  Sheets("Sheet1").Select
  Range("C1").Select
  MsgBox "Formatting Completed! Report Ready!"
End Sub

 

[rrossi]

Worked great! Thank you for all your help!

 

[DanteAmor]

Im glad to help you, thanks for the feedback.

 

[rrossi]

Worked great! Thank you for all your help!

Well, it worked one time and then gave me the following error when I ran it the second time:

Run time error '91'
Object variable or block variable not set

Here is the macro with the actual tab names:

Sub Format_report()
'
' Format_report Macro
'
Dim sh As Worksheet
Dim c As Range

Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
ActiveSheet.AutoFilter.ApplyFilter

For Each sh In Sheets
Select Case sh.Name
Case "LP Weekly Report", "PA Weekly Report", "PV Weekly Report", "PW Weekly Report", "SJ Weekly Report"
For Each c In sh.Range("U1:AD1").Cells
If c.Value = "Hide" Then
c.EntireColumn.Hidden = True
End If
Next c
For Each c In sh.Range("c7:c532")
If c <> "" Then
If c = 0 Then c.EntireRow.Hidden = True
End If
Next c
End Select
Next sh
Sheets("Consolidated Weekly Report").Select
Range("C1").Select
MsgBox "Formatting Completed! Report Ready!"
End Sub

Any thoughts on how to correct? Thank you!

 

[DanteAmor]

Delete this line:

ActiveSheet.AutoFilter.ApplyFilter

 

[rrossi]

That worked! Thank you so much!