Error help

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188
I'm new to VBA and i am struggling now a macro i've written has a error. The error coming up is ru-time error "9" subscript out of range. When i run debug the following line is highlighted Sheets("Entitlement 07-08").Activate
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Have you checked there is a sheet with that name in the active workbook?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Can we see the rest of the code?

Have you checked for trailing/leading spaces?

Are you working wih more than 1 workbook?
 

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188

ADVERTISEMENT

The Macro hides and unhides sheets based on a value in a seperate sheet called ASK ! The first macro does everything i want but the second one comes up with the error
Macro 1
Application.ScreenUpdating = False
If Range("V23").Value = "True" Then

Sheets("OP's 06-07").Activate
ActiveSheet.Visible = True
Sheets("TL's 06-07").Activate
ActiveSheet.Visible = True
ActiveSheet.Visible = True
Sheets("Entitlement 06-07").Activate
ActiveSheet.Visible = True
Sheets("Bank holidays 06-07").Activate
ActiveSheet.Visible = True

Sheets("ASK !").Select
End If
If Range("V23").Value = "False" Then
Sheets("OP's 06-07").Activate
ActiveSheet.Visible = False
Sheets("TL's 06-07").Activate
ActiveSheet.Visible = False
ActiveSheet.Visible = False
Sheets("Entitlement 06-07").Activate
ActiveSheet.Visible = False
Sheets("Bank holidays 06-07").Activate
ActiveSheet.Visible = False
Sheets("ASK !").Select
End If

Macro 2
Application.ScreenUpdating = False
If Range("V24").Value = "True" Then

Sheets("OP's 07-08").Activate
ActiveSheet.Visible = True
Sheets("TL's 07-08").Activate
ActiveSheet.Visible = True
Sheets("Entitlement 06-07").Activate
ActiveSheet.Visible = True
Sheets("Bank holidays 06-07").Activate
ActiveSheet.Visible = True

Sheets("ASK !").Select
End If
If Range("V24").Value = "False" Then

Sheets("OP's 07-08").Activate
ActiveSheet.Visible = False
Sheets("TL's 07-08").Activate
ActiveSheet.Visible = False
Sheets("Entitlement 06-07").Activate
ActiveSheet.Visible = False
Sheets("Bank holidays 06-07").Activate
ActiveSheet.Visible = False
Sheets("ASK !").Select
End If
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Can you answer the questions I asked?

I can't see anything wrong with that code, though it could be considerably shortened.
Code:
Sheets("OP's 06-07").Visible = Sheets("ASK !").Range("V23").Value
Sheets("TL's 06-07").Visible = Sheets("ASK !").Range("V23").Value
Sheets("Entitlement 06-07").Visible = Sheets("ASK !").Range("V23").Value
Sheets("Bank holidays 06-07").Visible = Sheets("ASK !").Range("V23").Value
 

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188
You hit the nail on the head. I must have had a trailing space. I named the sheet again and the macroworked. Thanks for your help. as for shortening the code i'm still in my early days of VBA
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,706
Members
410,630
Latest member
Maggie28
Top