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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
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,303
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
76,303
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,140,937
Messages
5,703,260
Members
421,289
Latest member
fbohlandt

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