Error: Consolidate method of Range Class failed

[KrisBrady]

I've seen similar issues before, but can't remember how I solved it in the past:

File A consolidates information from various files that are typically stored in the same directory as file A. It works on certain computers, but on others the user receives the "Consolidate method of Range class failed" error message.

I've tried this on my work laptop (works), logged in as myself on a training machine (doesn't work), and four other users (one works, three don't). We are all running the same version of Excel - Excel 2003 (11.8211.8202) SP3. Is there a setting that I'm missing somewhere?

The error message occurs when the macro code (module macro) hits the line in red text below:

    ConsolFileName = ActiveWorkbook.Name
    Sheets("Instructions").Select
    SubDivFilePath = Range("FilePath").Value
    ChDir SubDivFilePath
    Set rnRange = Range("SubdivisionFiles").CurrentRegion
    rnRange.Select
    Set rnRange = rnRange.Resize(rnRange.Rows.Count, 1)
    rnRange.Select
    ReDim aSubfiles(1 To rnRange.Rows.Count)
 
    For nCount = 1 To rnRange.Rows.Count
        aSubfiles(nCount) = "[" & rnRange(nCount, 1).Value & ".xls]A!R8C3:R209C17"
 
    Next
    Sheets("A").Select
    Range("c8").Select
    Selection.Consolidate sources:=aSubfiles, _
                                  Function:=xlSum
    For nCount = 1 To rnRange.Rows.Count
        aSubfiles(nCount) = "[" & rnRange(nCount, 1).Value & ".xls]A!R8C19:R209C31"
    Next

I looked through some of the other posts, but didn't find this specific issue and I tried some of the other solutions posted for other problems, to no avail.

I feel like a moron because I should know how to fix this, but my mind has gone blank.

Any help, advice, pointers or outright laughter at my predicament would be most appreciated. Thanks.

Kris

 

[KrisBrady]

I think that I made a mistake by including the word "Error" in the title of the above post - it seems that this may prevent some users from replying to this post.

I'm still stuck with this problem, but a new wrinkle has surfaced today - I can no longer run this on my laptop...hmmm. My technical team is trying to help with this issue - the issue does not appear to be permissions based as our system 'admin' user also received the error.

Any assistance would be appreciated.

Thanks.