Run-time error 9 Subscript out of range

Hi Folks,

The code placed below is throwing an error ( "Run-time error 9 Subscript out of range") on the following lines in the code placed at the end of this post:

Set WrkBk0 = Workbooks(Wk0)
Set WrkBk1 = Workbooks(Wk1)

Tried but failed to figure out the reason.

Can somebody help please?

Thanks in advance

Regards,

Amber
-------------------------------------------------------------------------------------------------------------------------------------------
Sub Topfive()

Dim WrkBk0 As Workbook
Dim WrkBk1 As Workbook
Dim WrkSt1 As Worksheet
Dim WrkSt2 As Worksheet
Dim WrkSt3 As Worksheet
Dim Wk0, Wk1 As String
Dim M1 As String
Dim M0 As String
Dim Y0, Y1 As Integer
Dim D As Variant
M0 = Format(DateAdd("M", -2, Date), "mmm")
M1 = Format(DateAdd("M", -1, Date), "mmm")
Y0 = Format(DateAdd("m", -1, Date), "yy")
Y1 = Format(DateAdd("m", -2, Date), "yy")
Wk0 = "Performance Master_" & M0 & " " & Y0 & ".xlsm"
Wk1 = "Performance Master_" & M1 & " " & Y1 & ".xlsm"
Set WrkBk0 = Workbooks(Wk0)
Set WrkBk1 = Workbooks(Wk1)
Set WrkSt1 = Sheets("Performance Summay")
Set WrkSt2 = Sheets("Sheet1")
Set WrkSt3 = Sheets("Performance Summary")

End Sub
------------------------------------------------------------------------------------------------------------------------------------------

If you copied that directly from your VBA, the 4th line from the bottom has "Summary" spelled incorrectly. Could that be it?

Your workbook needs to be "Open" to set the object reference. e.g.

Please Login or Register  to view this content.

Thanks for the responses.

I am facing problem while setting the value for the variables WrkBk0 and WrkBk1.

everything else is working fine.

the error pops up on the the following line of the code:
Set WrkBk0 = Workbooks(Wk0)

Looking forward for a solution...do let me know in case any futher clarity is required.

----------------------------------------------------------------------------------------------------------------------------------------------------------

Hi minnesotaart...the spelling doesnt seem to be a problem as the sheet name is exactly the same as referred in the code.

Hi Rudis...Thanks for pointing these out...hv corrected the same in my code, however as u rightly thought, the same is not the reason behind the error.

Hi Kenneth...the error was thrown up despite all the files referred to in the code were open while executing the code.

Hi Rudis,

Hv made the suggested changes still there is a problem with the code.

I am attaching the workbook with the code for your reference...plz advice.

Regards,

Amber

You have several issues that were not fixed.
e.g.

Please Login or Register  to view this content.

You are setting the left side as a worksheet? That is not how you dimmed that variable.

You did not use the function that I suggested. We do not have the workbooks to open to test.

Thanks Kenneth...got ur point...will keep that in mind.

Hi Kenneth,

Can you help me with identifying the issue in code named "top five".

Is there anything wrong with the way I am assingning value to the variable "WrkSt1"

Thanks in advance.

Amber

I suggest using a problem solving method that I used for years. Break the problem down to the most simple level possible. It not only helps you but would help others help you more readily.

Syntax errors aside, it really boils down to, did you define the workbook name that is open properly, or not. IsWorkBookOpen() will give you a quick way to find out. It is a good thing to check before setting the workbook object as a standard way to catch errors during run-time.

Reusing workbooks or even using ThisWorkbook and setting it to another workbook object can be fussy sometimes.

Here is how I tested your code and did a bit of tidying up.

Please Login or Register  to view this content.