VBA code to work in multiple worksheet

[help_andy]

I know next to nothing about VBA

I have VBA code that works in a worksheet and i would like it to work in an identical worksheet but it always gives me an error message

this is the code

Sub PreviousMonth()
If thisworkbook.Sheets("Admin).Range("A3").Value = 1 Then
Exit Sub
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
End Sub

Sub NextMonth()
If ActiveSheet.Range("A3").Value = 12 Then
Exit Sub
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
End Sub

 

[johnnyL]

Ok @help_andy

What do you do that do that file to start giving you the error?

I think I found the issue, Lemme see.

 

[johnnyL]

@help_andy See if the following works for you:

Sub PreviousMonth()
'
    Dim ws As Worksheet
    Set ws = Sheets("Leave Tracker")                        ' <--- Set this to sheet name that you want
'
    If ws.Range("A3").Value = 1 Then
        Exit Sub
    Else:
        ws.Range("A3").Value = ws.Range("A3").Value - 1
        ws.Columns("B:NI").Hidden = True
        Range(Columns(ws.Range("A3").Value * 31 - 29), Columns(ws.Range("A3").Value * 31 + 1)).Hidden = False
    End If
End Sub

Sub NextMonth()
'
    Dim ws As Worksheet
    Set ws = Sheets("Leave Tracker")                        ' <--- Set this to sheet name that you want
'
    If ws.Range("A3").Value = 12 Then
        Exit Sub
    Else:
        ws.Range("A3").Value = ws.Range("A3").Value + 1
        ws.Columns("B:NI").Hidden = True
        Range(Columns(ws.Range("A3").Value * 31 - 29), Columns(ws.Range("A3").Value * 31 + 1)).Hidden = False
    End If
End Sub

And what are 'triagle' icons?

 

[Michael M]

I'm still a bit confused, but does this do it ??

Sub PreviousMonth()
Dim ws As Worksheet
Set ws = ActiveSheet
    If ws.Range("A3").Value = 1 Then
        Exit Sub
        Else:
            ws.Range("A3").Value = ws.Range("A3").Value - 1
            ws.Columns("B:NI").Hidden = True
            Range(Columns(ws.Range("A3").Value * 31 - 29), Columns(ws.Range("A3").Value * 31 + 1)).Hidden = False
    End If
If ws.Range("A3").Value = 12 Then
        Exit Sub
    Else:
        ws.Range("A3").Value = ws.Range("A3").Value + 1
        ws.Columns("B:NI").Hidden = True
        Range(Columns(ws.Range("A3").Value * 31 - 29), Columns(ws.Range("A3").Value * 31 + 1)).Hidden = False
    End If
End Sub

 

[Michael M]

@johnnyL
And what are 'triagle' icons?

They are the Triangle Shapes either side of the Month text on row 3

 

[johnnyL]

LOL I know, I was just pointing out the misspelling in the file.

 

[johnnyL]

@Michael M The code you submitted is flawed.

For example if A3 > 1 & A3 < 12, Which of the 'Else' statements should be executed?

 

[johnnyL]

I suppose the code I submitted could be shortened to:

Sub PreviousMonth()
'
    Dim ws As Worksheet
    Set ws = Sheets("Leave Tracker")                        ' <--- Set this to sheet name that you want
'
    If ws.Range("A3").Value = 1 Then Exit Sub
'
    ws.Range("A3").Value = ws.Range("A3").Value - 1
    ws.Columns("B:NI").Hidden = True
    Range(Columns(ws.Range("A3").Value * 31 - 29), Columns(ws.Range("A3").Value * 31 + 1)).Hidden = False
End Sub

Sub NextMonth()
'
    Dim ws As Worksheet
    Set ws = Sheets("Leave Tracker")                        ' <--- Set this to sheet name that you want
'
    If ws.Range("A3").Value = 12 Then Exit Sub
'
    ws.Range("A3").Value = ws.Range("A3").Value + 1
    ws.Columns("B:NI").Hidden = True
    Range(Columns(ws.Range("A3").Value * 31 - 29), Columns(ws.Range("A3").Value * 31 + 1)).Hidden = False
End Sub