Hiding sheets based on a list

[Nicole_mattos]

Hi,

I want to hide sheets that are not in a list, so if a have a range("A1: A10") that could variaty of size, i want the sheets that not appear in the list to hide/exclude in the excel.
And, if possible, with the sheets that remain create a PDF.
I try to creat a validantion lis but it didn't work cause of a maximun selection.

Thank u.

 

[rlv01]

I am not going to address your PDF creation question, but one way to hide the sheets based on a list can be done using VBA

Book2
	A
1	Worksheets
2	Region 1 Sales Data
3	Region 2 Sales Data
4	Region 3 Sales Data
5	Region 4 Sales Data
6	Region 5 Sales Data
7	Region 6 Sales Data
8	Region 7 Sales Data
9	Region 8 Sales Data
10	Region 9 Sales Data
Visible Sheet List

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, CellRange As Range
    Dim WS As Worksheet
   
    If Not Application.Intersect(Me.Range("A:A"), Target) Is Nothing And Target.Cells.Count = 1 Then
        Application.ScreenUpdating = False
        
        With Me
            Set CellRange = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        End With
       
        With ThisWorkbook
            For Each WS In .Worksheets
                Select Case WS.Name
                    Case Me.Name
                    Case Else
                        Set R = Nothing
                        Set R = CellRange.Find(What:=WS.Name, LookAt:=xlWhole)
                       
                        If Not R Is Nothing Then
                            WS.Visible = xlSheetVisible
                        Else
                            WS.Visible = xlSheetHidden
                        End If
                End Select
            Next WS
        End With
        Application.ScreenUpdating = True
    End If
End Sub

 

[Nicole_mattos]

Hi, thanks for your answer

I was trying to replicate the code to find how i could implement in my case, but when i try to run it stops in Else, with the error 428
So i tried others codes and came up with this.
But when it cames to wsPrin appear a 91 error.
Could you help?

Sub ocultarPlanilhas()

Dim ws As Worksheet
Dim wsPrin As Worksheet
wsPrin = ThisWorkbook.Worksheets("Visible Sheet List").Select

For Each ws In Worksheets
If ws.Name <> wsPrin.Range("A1:A10").Value Then
ws.Visible = xlSheetHidden
End If
Next ws

End Sub

 

[rlv01]

This code you posted:

Sub ocultarPlanilhas()

    Dim ws As Worksheet
    Dim wsPrin As Worksheet
    wsPrin = ThisWorkbook.Worksheets("Visible Sheet List").Select

    For Each ws In Worksheets
        If ws.Name <> wsPrin.Range("A1:A10").Value Then
            ws.Visible = xlSheetHidden
        End If
    Next ws

End Sub

Contains many errors and is not workable. Try this instead:

Sub ocultarPlanilhas()
    Dim R As Range, CellRange As Range
    Dim WS As Worksheet
    Dim wsPrin As Worksheet

    On Error Resume Next
    Set wsPrin = ThisWorkbook.Worksheets("Visible Sheet List")
    On Error GoTo 0

    If wsPrin Is Nothing Then
        MsgBox "Missing required worksheet 'Visible Sheet List - Abort'", vbOKOnly Or vbCritical, Application.Name
        Exit Sub
    End If

    Application.ScreenUpdating = False

    With wsPrin
        Set CellRange = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With

    With ThisWorkbook
        For Each WS In .Worksheets
            If WS.Name <> wsPrin.Name Then
                Set R = Nothing
                Set R = CellRange.Find(What:=WS.Name, LookAt:=xlWhole)

                If Not R Is Nothing Then
                    WS.Visible = xlSheetVisible
                Else
                    WS.Visible = xlSheetHidden
                End If
            End If
        Next WS
    End With
    Application.ScreenUpdating = True
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com