Subscript out of range

**bishoposiris** · 08-06-2009, 10:33 AM

edit: I found the issue for this. I had the worksheet (VCollinsville) misspelled in the Workbook.

I have the following code (it's long but my problem is at the end where I stop):

Option Explicit

Sub Consolidate()
    Dim MyPath As String
    Dim FilesInPath As String
    Dim MyFiles() As String
    Dim Fnum As Long
    Dim mybook As Workbook
    Dim CalcMode As Long
    Dim sh As Worksheet
    Dim ErrorYes As Boolean
    Dim DCLastRow As Integer 'DirectorCopy
    Dim MCLastRow As Integer 'Monthly Compiler
    Dim CMonth As String 'Compile Month
    Dim CYear As String 'Compile Year
    Dim Month As Integer
    Dim MonthFilter As String
    Dim MCStartRow As Integer 'Monthly Compiler

    Dim center(18) As String
    center(1) = "Bardstown"
    center(2) = "Bothell"
    center(3) = "VCollinsville"
    center(4) = "El Paso"
    center(5) = "Evansville"
    center(6) = "Greensboro"
    center(7) = "VHeathrow"
    center(8) = "Joplin"
    center(9) = "Kennesaw"
    center(10) = "Lafayette"
    center(11) = "Malvern"
    center(12) = "VManhattan"
    center(13) = "VMansfield"
    center(14) = "VOttawa"
    center(15) = "VPonco City"
    center(16) = "VReno"
    center(17) = "VSioux City"
    center(18) = "VTerra Haute"
    
    Dim FileCount As Long
    Dim ScoringAve As Double
    Dim i As Long
    
    Workbooks("Monthly PF Compiler").Activate
    
'    If Cells(13, 4).Value = "January" Then Month = 1
'    If Cells(13, 4).Value = "February" Then Month = 2
'    If Cells(13, 4).Value = "March" Then Month = 3
'    If Cells(13, 4).Value = "April" Then Month = 4
'    If Cells(13, 4).Value = "May" Then Month = 5
'    If Cells(13, 4).Value = "June" Then Month = 6
'    If Cells(13, 4).Value = "July" Then Month = 7
'    If Cells(13, 4).Value = "August" Then Month = 8
'    If Cells(13, 4).Value = "September" Then Month = 9
'    If Cells(13, 4).Value = "October" Then Month = 10
'    If Cells(13, 4).Value = "November" Then Month = 11
'    If Cells(13, 4).Value = "December" Then Month = 12
'    CMonth = MonthName(Month, True)
    'This one line of code replaces the above 13 lines
    CMonth = Left(Cells(13, 4).Value, 3)
    CYear = Right(Cells(13, 7).Value, 2)

           
    'Fill in the path\folder where the files are
    MyPath = "X:\C&A Analysts Team\PF Process\1 Tally & PF's Work in Progress\Centers\"
    
    For i = 1 To 18
        
'        'Add a slash at the end if the user forget it
'        If Right(MyPath, 1) <> "\" Then
'            MyPath = MyPath & "\"
'        End If
        
        'If there are no Excel files in the folder increment i and continue
        MonthFilter = MyPath & center(i) & "\*" & CMonth & " " & CYear & "*.xl*"
        FilesInPath = Dir(MonthFilter)
        
        If FilesInPath = "" Then
            MsgBox "No files found in " & center(i)
            GoTo ContinueLoop
        End If
        
        If FilesInPath <> "" Then
            FileCount = FileCount + 1
        End If
        
        'Fill the array(myFiles)with the list of Excel files in the folder
        'that match the Month and Year selected
        Fnum = 0
        Do While FilesInPath <> ""
            If InStr(1, FilesInPath, CMonth & " " & CYear, vbTextCompare) Then
                Fnum = Fnum + 1
                ReDim Preserve MyFiles(1 To Fnum)
                MyFiles(Fnum) = FilesInPath
                FilesInPath = Dir()
            End If
        Loop
    
        'Change ScreenUpdating, Calculation and EnableEvents
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        'Loop through all files in the array(myFiles)
        MCStartRow = 1
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & center(i) & "\" & MyFiles(Fnum))
                On Error GoTo 0
                
                If Not mybook Is Nothing Then
    
                    'Need to do the following:
                    'if lazy eye hasn't been run in directorcopy then run it
                    With mybook.Worksheets("DirectorCopy")
                        If .Cells(1, 1) = "" Then
                            'Application.Run "DirectorFormat"
                            Application.Run "'Test Tally SheetIII.xlsm'!DirectorFormat"
                            DCLastRow = .Range("A" & Rows.Count).End(xlUp).Row
                        'Determine how many rows in directorcopy
                        Else
                            DCLastRow = .Range("A" & Rows.Count).End(xlUp).Row
                        End If
                        
                        mybook.Worksheets("DirectorCopy").Activate
                        'Copy center,month,week, analyst,uid each to the first 5 columns in compiler
                        .Cells(2, 2).Copy (Workbooks("Monthly PF Compiler").Worksheets(center(i)).Cells(MCStartRow, 1))
                        .Cells(1, 1).Copy (Workbooks("Monthly PF Compiler").Worksheets(center(i)).Cells(MCStartRow, 2))
                        .Cells(2, 1).Copy (Workbooks("Monthly PF Compiler").Worksheets(center(i)).Cells(MCStartRow, 3))
                        .Cells(1, 2).Copy (Workbooks("Monthly PF Compiler").Worksheets(center(i)).Cells(MCStartRow, 4))
                        .Cells(1, 4).Copy (Workbooks("Monthly PF Compiler").Worksheets(center(i)).Cells(MCStartRow, 5))

When I get to here:

.Cells(2, 2).Copy (Workbooks("Monthly PF Compiler").Worksheets(center(i)).Cells(MCStartRow, 1))

I get the subscript out of range error. What's odd is it works just fine for the previous folder (Bothell) but when it gets here in the next folder (VCollinsville) I get this error. What could be causing this?

**Andy Pope** · 08-06-2009, 10:38 AM

If it works then the obvious question is does the worksheet VCollinsville actually exist?

A simple test would be to go to the Bothell worksheet and type,

CTRL+G
VCollinsville!A1

Do you get to the right place?

**Andy Pope** · 08-06-2009, 10:42 AM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

**bishoposiris** · 08-06-2009, 10:42 AM

Yes the code sees 3 spreadsheets in the folder VCollinsville. It opens the first one but when it tries to copy cell B2

.Cells(2, 2).Copy (Workbooks("Monthly PF Compiler").Worksheets(center(i)).Cells(MCStartRow, 1))

I get the subscript out of range error. It pulled the spreadsheet from Bothell and executed perfectly which is why I'm confused. All the spreadsheet layouts are the same.