RD PH/Cloud How-To: Hide unwanted sheets using Excel macros

Summary

This article provides a guide on how to hide unwanted sheets using Excel macros.

Article sections:

• Method
• Implementation

Method

If you can't filter out values that will generate undesirable sheets using the normal options the only remaining option is to use Excel VBA macros. The only macro we check for is OSR_ReportComplete(). To demonstrate and document I created these two examples:

Some notes about this feature

1. It works in Excel only.
2. This macro slows down reports, if your workbook expands to 50+ sheets, there may be noticeable performance degradation applying this macro. 

Implementation

1. Report that uses sheet per value and where some sheets have undesirable values (amount is blank/zero):
   
2. Report where sheet per value is used, where the macro is enabled:
   

The macro code itself:

Sub OSR_ReportComplete()
On Error GoTo ErrHandler

Dim Sht as Worksheet
Application.Screenupdating = False
Application.DisplayAlerts = False

For Each Sht in Application.Worksheets

If Sht.Range ("A1").Value <= 0 then 
'Sht.Visible = xlSheetHidden
Sht.Delete
Else
Cells(4,2) = "OSR_ReportComplete approves."
End If

Next Sht
Application.DisplayAlerts = True

ExitHandler: 
ExitSub

ErrHandler: 
MsgBox (Err.Number & vbCrLf & Err.Description)
GoTo ExitHandler
End Sub