Errors when VBA Functions are Combined

[wedzmer]

Badly need help here...

I wanted to combine these functions with one worksheet.

First: I have a dual function VBA for Hiding and Unhiding Rows & Columns.
Second: I also single function COPY SHEET button.

Both buttons work perfectly when not combined in one sheet. The situations I have observed are:

1. When the COPY SHEET BUTTON is used along with the HIDE ROWS button alone, there is nothing wrong. Everything works perfectly.
2. When the HIDE ROWS BUTTON is used with HIDE COLUMNS VBA, there is nothing wrong. Everything works perfectly.
3. When all the functions, COPY SHEET BUTTON and the HIDE ROWS/COLUMNS VBA are combined in one single sheet... ERROR HAPPENS when I click the buttons.

Here is the original VBA Code that presents error:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Cells.Count > 1 Then Exit Sub
   
   Application.EnableEvents = False
   
   On Error GoTo ErrH
   If Target.Address = "$AN$7" Then
       Me.Name = Target
       Application.EnableEvents = True
       Exit Sub
   End If
   
   If Not Intersect(Target, Range("b12:B131")) Is Nothing Then
       Target = UCase(Target)
   End If
   
   Application.EnableEvents = True
   Exit Sub
   
ErrH:
   MsgBox "Invalid Worksheet Name"
   Application.EnableEvents = True
   
End Sub

Sub HideRow()
Dim lLastRow As Long
Dim lCounter As Long
Dim Rng As Range
Dim Counter As Boolean
Counter = True

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    With ActiveSheet
    
        .Unprotect ("09193483966")
        .Shapes("Button 1").Visible = False

For Each CL In Range("HM200:KO200")
If CL.Value = 1 Then
    If Counter Then
        Set Rng = CL
        Counter = False
    Else
        Set Rng = Union(Rng, CL)
    End If
End If
Next CL
Rng.EntireColumn.Hidden = True


       lLastRow = .Range("E1000").End(xlUp).Row
        For lCounter = 14 To lLastRow
            If .Cells(lCounter, "E").Value = 1 Then
                .Cells(lCounter, "E").EntireRow.Hidden = True
                'Else: .Cells(lCounter, "GM").EntireRow.Hidden = False
            End If
        Next lCounter
        .Range("G12").Select

        .Shapes("Button 2").Visible = True
    End With

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    ActiveSheet.Protect ("09193483966"), DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Sub UnHideRow()

    With ActiveSheet
        .Unprotect ("09193483966")
        .Rows.Hidden = False
        .Columns.Hidden = False
        .Range("A:F").Columns.Hidden = True
        .Shapes("Button 1").Visible = True
        .Shapes("Button 2").Visible = False
        .Protect ("09193483966"), DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    End With
    
End Sub


Sub CopySheet()
    ActiveSheet.Copy After:=ActiveSheet
End Sub

and here is the file I'm working on.. if there is any password being asked, please use "09193483966".

Academic Teacher ECR File

Thanks for anyone who would like to help...

 

[jbeaucaire]

Crossposted here.