This article provides a guide on how to hide unwanted sheets using Excel macros.
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
- It works in Excel only.
- This macro slows down reports, if your workbook expands to 50+ sheets, there may be noticeable performance degradation applying this macro.
- Report that uses sheet per value and where some sheets have undesirable values (amount is blank/zero):
- Report where sheet per value is used, where the macro is enabled:
The macro code itself:
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
Cells(4,2) = "OSR_ReportComplete approves."
Application.DisplayAlerts = True
MsgBox (Err.Number & vbCrLf & Err.Description)