if then executes every time

[jrowe]

I have an If Then statement the executes every time, true or false doesn't matter.

Option Explicit

Sub AddMonthWkst()
    Dim ws As Worksheet
    Dim wsM As Worksheet
    Dim strName As String
    Dim bCheck As Boolean

    On Error Resume Next
    strName = Format(Date, "mmmm yyyy")
    bCheck = Len(Sheets(strName).Name) > 0

    If bCheck = False Then
        ThisWorkbook.Unprotect "MyPass"
        Worksheets("MASTER").Visible = xlSheetVisible 'unhide tab just in case hidden
        Set wsM = Sheets("MASTER")
        wsM.Copy Before:=Sheets(1)
        ActiveSheet.Tab.Color = RGB(146, 208, 80)
        ActiveSheet.Name = strName
        For Each ws In ActiveWorkbook.Worksheets
            Dim wtf As String
            wtf = ws.Name
            If (wtf <> strName) Or (wtf <> "MASTER") Then
                ws.Visible = xlSheetHidden
            End If
        Next ws
        ThisWorkbook.Protect "MyPass"
    End If

    Set wsM = Nothing
End Sub

It is supposed to create the new month then hide last moths tab leaving the new month and MASTER showing. No matter what I try it always hides the newly created tab and the MASTER tab if another tab is open. If the MASTER is the only tab open then it will create the new tab and hide it leaving the MASTER visible.

 

[dmt32]

Hi
welcome to forum
untested but see if this update to your code does what you want

Option Explicit
Sub AddMonthWkst()
    Dim ws          As Worksheet, wsM  As Worksheet
    Dim strName     As String
    Dim bCheck      As Boolean
   
    Const MyPassword As String = "MyPass"
   
    strName = Format(Date, "mmmm yyyy")
   
    With ThisWorkbook
        bCheck = Evaluate("isref('" & strName & "'!A1)")
        If bCheck = False Then
            .Unprotect MyPassword
           
            Set wsM = .Worksheets("MASTER")
            wsM.Visible = xlSheetVisible        'unhide tab just in case hidden
            wsM.Copy Before:=.Sheets(1)
           
            With .ActiveSheet
                .Tab.Color = RGB(146, 208, 80)
                .Name = strName
            End With
           
            For Each ws In .Worksheets
                Select Case ws.Name
                    Case strName, wsM.Name
                        ws.Visible = xlSheetVisible
                    Case Else
                        ws.Visible = xlSheetHidden
                End Select
            Next ws
        End If
       
       .Protect MyPassword
    End With
    Set wsM = Nothing
End Sub

Dave

 

[jrowe]

Hi
welcome to forum
untested but see if this update to your code does what you want

Option Explicit
Sub AddMonthWkst()
    Dim ws          As Worksheet, wsM  As Worksheet
    Dim strName     As String
    Dim bCheck      As Boolean
  
    Const MyPassword As String = "MyPass"
  
    strName = Format(Date, "mmmm yyyy")
  
    With ThisWorkbook
        bCheck = Evaluate("isref('" & strName & "'!A1)")
        If bCheck = False Then
            .Unprotect MyPassword
          
            Set wsM = .Worksheets("MASTER")
            wsM.Visible = xlSheetVisible        'unhide tab just in case hidden
            wsM.Copy Before:=.Sheets(1)
          
            With .ActiveSheet
                .Tab.Color = RGB(146, 208, 80)
                .Name = strName
            End With
          
            For Each ws In .Worksheets
                Select Case ws.Name
                    Case strName, wsM.Name
                        ws.Visible = xlSheetVisible
                    Case Else
                        ws.Visible = xlSheetHidden
                End Select
            Next ws
        End If
      
       .Protect MyPassword
    End With
    Set wsM = Nothing
End Sub

Dave

Thanks, that worked perfectly, never thought to try select statements.

 

[dmt32]

Thanks, that worked perfectly, never thought to try select statements.

most welcome glad update resolves your issue & appreciate your feedback

Dave