Hide sheets in a Bill of Material

**ajbele** · 04-17-2013, 09:22 AM

I have created a workbook that generates a complete bill of material for a selected model by un-hiding specified worksheets. Each worksheet contains a sub-assembly that is relevant to the user selected model. However, some selected models do not contain all sub-assemblies, which is where the issue occurs. If a user generates a bill of material for a model containing all sub-assemblies, the macro will un-hide all pertinent sub-assemblies without issue. Then if the user selects another model that does not contain all sub-assemblies and generates a bill of material, sub-assembly worksheets from the first generated bill of material remain un-hidden. What is the best way to hide worksheets leftover from the prior generation by the user. The code thus far is as follows:

Sub GetSheets()

On Error Resume Next
numwrksheets = ActiveWorkbook.Worksheets.Count
w = 1
For w = 1 To numwrksheets
Worksheets(w).Visible = False
Next w

Worksheets("MODEL SELECTION SHEET").Visible = True

Excel.Worksheets("MODEL SELECTION SHEET").Activate
selectedmodel = Excel.ActiveSheet.Range("P1").Text
'x = MsgBox(model, vbOKCancel, "variable content")
Excel.Worksheets("index").Activate

n = 1

For n = 1 To 24

rnge = "A" & n

model = ActiveSheet.Range(rnge).Text

If selectedmodel = model Then Row = n
Next n

Excel.Worksheets((ActiveSheet.Range(("B" & Row)).Text)).Visible = True
Excel.Worksheets((ActiveSheet.Range(("C" & Row)).Text)).Visible = True
Excel.Worksheets((ActiveSheet.Range(("D" & Row)).Text)).Visible = True
Excel.Worksheets((ActiveSheet.Range(("E" & Row)).Text)).Visible = True
Excel.Worksheets((ActiveSheet.Range(("F" & Row)).Text)).Visible = True
Excel.Worksheets((ActiveSheet.Range(("G" & Row)).Text)).Visible = True
Excel.Worksheets((ActiveSheet.Range(("H" & Row)).Text)).Visible = True
Excel.Worksheets((ActiveSheet.Range(("I" & Row)).Text)).Visible = True

Excel.Worksheets("index").Visible = False

End Sub

**tehneXus** · 04-17-2013, 06:13 PM

Hi,

try this:

Option Explicit

Sub GetSheets()

On Error Resume Next

    Dim i As Long, j As Long, lngRow As Long
    Dim strSelectedModel As String
    Dim blnWsVisible As Boolean
    
    With ActiveWorkbook
        
        'get model
        strSelectedModel = .Worksheets("MODEL SELECTION SHEET").Range("P1").Value
        
        'find appropriate row in 'index' sheet
        For i = 1 To 24
            If strSelectedModel = .Worksheets("index").Range("A" & i).Value Then lngRow = i
        Next i
        
        'hide/unhide accordingly
        For i = 1 To .Worksheets.Count
            blnWsVisible = False
            For j = 2 To 9
                If .Worksheets(i).Name <> "MODEL SELECTION SHEET" Then
                    If .Worksheets(i).Name = .Worksheets("index").Cells(lngRow, j).Value Then
                        blnWsVisible = True
                        GoTo SetVisibility
                    End If
                Else
                    GoTo SkipWs
                End If
            Next j
SetVisibility:
            If .Worksheets(i).Visible <> blnWsVisible Then .Worksheets(i).Visible = blnWsVisible
SkipWs:
        Next i
        
    End With

End Sub

like used in this example: Hide sheets in a Bill of Material.xlsm

Mark this thread SOLVED if this answer solved this problem.

Regards