VBA Macro 1004 Runtime Error When Copying a Worksheet

[BYE2]

Hello Guys! Take it easy on me. I'm an intermediate Excel User, but a NOVICE VBA user .

I downloaded an employee leave sheet and have pretty much got it operating as I need it to...EXCEPT... I copied the original "2019-2020 Leave" worksheet (so that I can have a 2020-2021 sheet). But now when I try to use the monthly scroll bars on the newly copied "2020-2021" sheet, I get a debug error. EEEEKKKKK. "Run-time error '1004: Unable to set the Hidden property of the Range class"

.....Please help!

Here's a link to the sheet : Leave Sheet

Here's the code: Red letters are of course highlighted in yellow inVBA:


Sub PreviousMonth()

ActiveSheet.Unprotect "MHVhr20"
If ActiveSheet.Range("A3").Value = 1 Then
GoTo Finish
Else:
Range("A3").Value = Range("A3").Value - 1
LeaveTracker.Columns("B:NI").Hidden = True
LeaveTracker.Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
End If
Finish: ActiveSheet.Protect "MHVhr20"
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Remove "LeaveTracker"

Columns("B:NI").Hidden = True
Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False

 

[BYE2]

Hi Fluff!
Thanks for the quick reply. Your suggested got the scroll bar moving without error
However, now when I scroll through each month, the month name doesn't change

 

[BYE2]

The 2020-2021 sheet is set to scroll from July to June of the following year (A1 = starting month, A2=starting year).
Also, when I removed the "LeaveTracker" I noticed that the year (along with the month), didn't change when I scrolled.... Thoughts??

There is also a hidden "Ref" worksheet.

Does this help?

 

[BYE2]

ENTIRE Leave Sheet

 

[Fluff]

That's because it's looking at G2 on sheet3, which is calculating based on the 2019-20 sheet.

 

[Fluff]

Put this formula in A4 on both sheets
=DATE(A2,(A1+A3-1),1)
Make sure the shape is called "Rectangle 1" on both sheets & use

Sub NextMonth()
ActiveSheet.Unprotect "MHVhr20"
        If ActiveSheet.Range("A3").Value = 12 Then
            GoTo Finish
        Else:
            Range("A3").Value = Range("A3").Value + 1
            With ActiveSheet.Shapes("Rectangle 1")
               .TextFrame.Characters.Text = Format(Range("A4"), "mmmm yyyy")
            End With
            Columns("B:NI").Hidden = True
            Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
        End If
Finish: ActiveSheet.Protect "MHVhr20"

End Sub