Run Time Error '9' Subscript out of Range

[Papa_Don]

Group,

I've created a large spreadsheet with a fair amount of code behide it. Historically it has run well and with no issues. However it has started giving me a "Run-time error '9': Subscript out of range" error. I have no idea what is causing it. It is stoping at the beginning of a new sub. The code says:

Windows("All Same Store Rate Plan Production.xlsx").Activate 'failure is here
sheetNo1 = ActiveWorkbook.Worksheets.Count

I can promise, the spreadsheet it is referring to is open. It is named correctly. Can anyone offer some ideas as to why this error is coming up?

In advance, thanks for your help.

Don

[ddanbe]

If possible: a little more code would be welcome.
Now I can only guess it has perhaps something to do with the number of sheets in the worksheet?

[Papa_Don]

ddanbe,

There are only 4 sheets in the workbook. For what it's worth, I'm rerunning the data (which creates these spreadsheets that are to be "activated"). I'm now going on the assumption that they my have been formatted incorrectly or have some kind of corruption to them. I say this as I've rerun the first one (which produced the original error) and it went through the macro fine. However the second one crashed. Thus the reason for recreating the data and spreadsheets.

If it is of interest, here's the code I've written:

Sub ImportReport1()

    Windows("All Same Store Rate Plan Production.xlsx").Activate
    sheetNo1 = ActiveWorkbook.Worksheets.Count

    'Sheet1
If sheetNo1 > 0 Then
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(1).Select
    name1 = Range("A12").Value
    name2 = Range("A13").Value
    If name1 = "Business Category" Then
        name2 = ""
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(1).Select
    Range("A1:K5").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data1").Select
    Range("B1").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(1).Select
    Range("A6:K11").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data1").Select
    Range("B7").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(1).Select
    Range("A12:K7000").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data1").Select
    Range("B13").Select
    ActiveSheet.Paste
    Range("A15").Select
    End If

    If name2 = "Business Category" Then
        name1 = ""
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(1).Select
    Range("A1:K11").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data1").Select
    Range("B1").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(1).Select
    Range("A13:K7000").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data1").Select
    Range("B13").Select
    ActiveSheet.Paste
    Range("A15").Select
    End If
End If

    'Sheet2/Four Points
If sheetNo1 > 1 Then
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(2).Select
    name1 = Range("A12").Value
    name2 = Range("A13").Value
    If name1 = "Business Category" Then
        name2 = ""
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(2).Select
    Range("A1:K5").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data81").Select
    Range("B1").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(2).Select
    Range("A6:K11").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data81").Select
    Range("B7").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(2).Select
    Range("A12:K7000").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data81").Select
    Range("B13").Select
    ActiveSheet.Paste
    Range("A15").Select
    End If

    If name2 = "Business Category" Then
        name1 = ""
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(2).Select
    Range("A1:K11").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data81").Select
    Range("B1").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(2).Select
    Range("A13:K7000").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data81").Select
    Range("B13").Select
    ActiveSheet.Paste
    Range("A15").Select
    End If
End If

    'Sheet3/Aloft
If sheetNo1 > 2 Then
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(3).Select
    name1 = Range("A12").Value
    name2 = Range("A13").Value
    If name1 = "Business Category" Then
        name2 = ""
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(3).Select
    Range("A1:K5").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data82").Select
    Range("B1").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(3).Select
    Range("A6:K11").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data82").Select
    Range("B7").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(3).Select
    Range("A12:K7000").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data82").Select
    Range("B13").Select
    ActiveSheet.Paste
    Range("A15").Select
    End If

    If name2 = "Business Category" Then
        name1 = ""
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(3).Select
    Range("A1:K11").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data82").Select
    Range("B1").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(3).Select
    Range("A13:K7000").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data82").Select
    Range("B13").Select
    ActiveSheet.Paste
    Range("A15").Select
    End If
End If

     'Sheet4/Element
If sheetNo1 > 3 Then
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(4).Select
    name1 = Range("A12").Value
    name2 = Range("A13").Value
    If name1 = "Business Category" Then
        name2 = ""
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(4).Select
    Range("A1:K5").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data83").Select
    Range("B1").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(4).Select
    Range("A6:K11").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data83").Select
    Range("B7").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(4).Select
    Range("A12:K7000").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data83").Select
    Range("B13").Select
    ActiveSheet.Paste
    Range("A15").Select
    End If

    If name2 = "Business Category" Then
        name1 = ""
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(4).Select
    Range("A1:K11").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data83").Select
    Range("B1").Select
    ActiveSheet.Paste
    Range("A15").Select
    Windows("All Same Store Rate Plan Production.xlsx").Activate
    Sheets(4).Select
    Range("A13:K7000").Select
    Selection.Copy
    Windows("YTD Rate Plan Production Master.xlsm").Activate
    Sheets("Data83").Select
    Range("B13").Select
    ActiveSheet.Paste
    Range("A15").Select
    End If
End If

If sheetNo1 = 1 Then
    Sheets("Four Points").Visible = False
    Sheets("Aloft").Visible = False
    Sheets("Element").Visible = False
    Exit Sub
End If

If sheetNo1 = 2 Then
    Sheets("Aloft").Visible = False
    Sheets("Element").Visible = False
    Exit Sub
End If

If sheetNo1 = 3 Then
    Sheets("Element").Visible = False
    Exit Sub
End If

End Sub

As you can see, there's nothing fancy here.

Thanks for the interest,

Don

[tinstaafl]

Not sure if this is the problem, but you sem to be assuming 1-based indexing. Have you tried 0-based indexing of the spreadsheets?

[ddanbe]

Indexing your sheets with numbers like in Sheets(4).Select is not often a good idea.
Say you have 3 sheets indexed in your code as 1 , 2 , 3.
If you delete sheet 2, indexing with 2 will in fact be what originally was sheet(3) in your code. Better is to name them.
Think that is not a problem in your code right now.

Now for your problem:
Windows("All Same Store Rate Plan Production.xlsx").Activate just activates an open workbook.
Is it open when you execute Sub ImportReport1()?

[Papa_Don]

ddanbe, yes, the workbook is open when the code is run. For what it's worth, I've since re-run the reports from our server, opened them, saved them to my hard drive and ran the script and it worked fine. This leaves me to believe the file had changed in some manner or was corrupt in some way. Is there a specific definition of what the "Run-time error 9" really means?

I'm very aware of the indexing the sheets as 1, 2, 3, etc. However I have no choice but to do it this way as the individual sheet names vary as to the information that is in them. Thankfully though, a sheet really doesn't have the opportunity to be deleted.

Thanks for the ideas. I'm still learning about programming. So I'm still at the stage where these issues are less of a problem and more of a learning experience/opportunity.

Don

[ddanbe]

Could it be that someone changed the filepaths or accesspermissions on the server? Or set the files as readonly?
I also took the liberty to tweak your code a bit.
Because your code contains a great deal of redundant code I was able to make a part into one routine that could be called many times.
Here is the result:

'Option Explicit
'See here why: http://msdn.microsoft.com/en-us/library/y9341s4f.aspx


'Constants are handy if you use the constant names in your code,
'you don't have to look and change the filenames(in case they would change)
'in all your code. You accidentily might oversee some.
Const cFromSheet As String = "All Same Store Rate Plan Production.xlsx"
Const cToSheet As String = "YTD Rate Plan Production Master.xlsm"

Private Sub CopyPaste(sheetNr As Integer, Rng1 As String, ToCopyto As String, Rng2 As String)
    Windows(cFromSheet).Activate
    Sheets(sheetNr).Select
    Range(Rng1).Select
    Selection.Copy
    Windows(cToSheet).Activate
    Sheets(ToCopyto).Select
    Range(Rng2).Select
    ActiveSheet.Paste
    Range("A15").Select
End Sub

Sub ImportReport1()

    Windows(cFromSheet).Activate
     'Sheet1
    If sheetNo1 > 0 Then
        Windows(cFromSheet).Activate 'twice?
        Sheets(1).Select
        name1 = Range("A12").Value
        name2 = Range("A13").Value
        If name1 = "Business Category" Then
            name2 = ""
            Call CopyPaste(1, "A1:K5", "Data1", "B1")
            Call CopyPaste(1, "A6:K11", "Data1", "B7")
            '*******These 2 calls replace 18 lines!!!=*================
            'you can more clearly see what the important stuff is
            'and can easily change it if needed
            'I leave the rest of your code for you to modify
            'if you would like to do it
            '**
            Windows("All Same Store Rate Plan Production.xlsx").Activate
            Sheets(1).Select
            Range("A12:K7000").Select
            Selection.Copy
            Windows("YTD Rate Plan Production Master.xlsm").Activate
            Sheets("Data1").Select
            Range("B13").Select
            ActiveSheet.Paste
            Range("A15").Select
        End If

        If name2 = "Business Category" Then
            name1 = ""
            Windows("All Same Store Rate Plan Production.xlsx").Activate
            Sheets(1).Select
            Range("A1:K11").Select
            Selection.Copy
            Windows("YTD Rate Plan Production Master.xlsm").Activate
            Sheets("Data1").Select
            Range("B1").Select
            ActiveSheet.Paste
            Range("A15").Select
            Windows("All Same Store Rate Plan Production.xlsx").Activate
            Sheets(1).Select
            Range("A13:K7000").Select
            Selection.Copy
            Windows("YTD Rate Plan Production Master.xlsm").Activate
            Sheets("Data1").Select
            Range("B13").Select
            ActiveSheet.Paste
            Range("A15").Select
        End If
    End If

    'Sheet2/Four Points
    If sheetNo1 > 1 Then
        Windows("All Same Store Rate Plan Production.xlsx").Activate
        Sheets(2).Select
        name1 = Range("A12").Value
        name2 = Range("A13").Value
        If name1 = "Business Category" Then
            name2 = ""

            '***** etc. etc. rest of code

I put some comments in I hope it is a bit clear, if not please ask.
If you would implement these changes, never throw away the original code before the changes work!
Happy programming. :)
Danny

[Papa_Don]

Danny,

Wow!! I love the thought process. Clearly you can see that, based on my limited programming skills, I've not completely understood the things that can be done via the "()" when you create your Sub. You just taught me some things. However I'm going to have questions for you when as work through this and try it out. I want to completely understand how it works.

This is awesome! Thanks!!

Don