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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,353
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
76,353
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
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
76,353
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
 

Forum statistics

Threads
1,175,942
Messages
5,900,436
Members
434,832
Latest member
bornsteij78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top