VBA Run-time error -2147414848 - Unqualified object problem???

[rootdown42]

This one is driving me crazy, so any and all help is appreciated.

I have to create individualized reports for our reps. To do so I have created one master template that contains the data that I need. The idea is to use VBA to perform the following steps:
- Loop through a list of the reps
- Filter the data in the template for selected rep
- Recalculate
- Save off that rep-specific version
- Open the just-saved rep-specific version
- Loop through each sheet in rep report and delete rows that contain no data (all 0's)
- Save Rep version
- Close Rep version

I have code that works effectively...sometimes. As in I can run the module successfully for all reports, but on subsequent runs I receive the dreaded "Run-time Error -2147417848 (80010108): Method PasteSpecial of object Range failed" when a I attempt a PasteSpecial on the first rep-specific version I open. At various times while messing with the code I have also received the same run-time error, but with the message "Automation Error. The object invoked has disconnected from its clients"

After reading every post I could find on this, I was directed to MSKB Q319832, dealing with unqualified code and global objects in Office. I'm almost positive this is the issue I have - that my references to the rep-specific workbook object is unqualified, hence causing the run-time errors. My problem is that I don't understand how to qualify objects correctly. This is what I'm hoping someone can help me with.

The following is what I believe to be the part of my code that is relevant to this issue

    Dim wbORep As Workbook
    Dim wsORep As Worksheet
    Dim strFileName As String    ' path for rep-version report

    Set wbORep = Nothing

        'Do a SaveAs in rep folder (file will recalculate during save)
        ActiveWorkbook.SaveCopyAs strFileName
        'Open saved file
        Set wbORep = Workbooks.Open(strFileName)
        
        Application.ScreenUpdating = False

            'Cycle through sheets
            For Each wsORep In wbORep.Worksheets
                    If wsORep.Name = "WorkbookData" Then Exit For
                    
                    'Freeze sheets
                    wsORep.Range("A1:AJ1000").Copy
                    wsORep.Range("a1:aj1000").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False   <----THIS IS WHERE IT FAILS
                    Application.CutCopyMode = False
                    Application.Goto reference:=wsORep.Range("A1")
                    wsORep.Visible = sheetVisible
                    
                    'Hide all rows of customers that are all 0's
                    rowSubtotal = wsORep.Range("F:F").Find(What:="Customer", _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False).Row + 1    'Determine header row and start loop one row lower
                    rowLastSummary = wsORep.Cells(rowSubtotal, 6).End(xlDown).Row
                    For c = rowSubtotal To rowLastSummary
                        If wsORep.Cells(c, 6).Value = "Grand Total" Then Exit For
                            Do While Application.CountIf(wsORep.Range(wsORep.Cells(c, 10), wsORep.Cells(c, 30)), "<>0") = 0
                                wsORep.Rows(c).EntireRow.Delete
                            Loop
                    Next c
            Next wsORep
            
            wbORep.Sheets("WorkbookData").Visible = xlHidden
            wbORep.Sheets("Curr Qtr").Activate
            wbORep.Close SaveChanges:=True

Even though it errors on the PasteSpecial line, I assume that the problem starts with

        Set wbORep = Workbooks.Open(strFileName)

Does the problem start here because the workbook object isn't qualified? If so, how should I alter this code to do so? I'm out of element here.

Oh, and for the record, I started this code by using With blocks for the wbORep and wsORep instead of qualifying them all the time. I got rid of them when trying to troubleshoot and will fix later. Even with the With blocks I received the same errors. Thanks.

 

[Ivan F Moala]

Have you tried Set wbORep = Nothing
AFTER wbORep.Close SaveChanges:=True

 

[rootdown42]

Ivan, yes, I do have that statement at the end of my code, right before the sub routine ends. Sorry, should have included that.

 

[Ragnar1211]

What if you did this instead of the copy and paste:

wsORep.Range("A1:AJ1000").formula = wsORep.Range("A1:AJ1000").value

 

[rootdown42]

Sorry Ragnar, but that just results in the same type of error, except this one is "Method 'Formula' of object 'Range' failed, which to me seems to reinforce the assumption that the problem is with the unqualified workbook object.

 

[Ragnar1211]

When the code breaks, what does the variable wsORep say when you place your mouse over it?

Are any of the sheets protected?

Are any of the sheets hidden/veryhidden?.

Are any of the rows filtered?

Something must be funny in one of the sheets, does the for/next loop do any of them, or does it break right away?

 

[rootdown42]

Afraid it doesn't say anything when I place my mouse over it, but I added it in the Watch section, and when the code breaks the Name for wsORep = "Curr Qtr", which is what I want, since its the first sheet in the workbook. Also when the code breaks, wbORep.Name equals the name of the first rep-specific report, so it also appears correct.

None of the sheets are protected.

Yes, there are a number of hidden worksheets in the rep-specific workbook. Most of them contain links to Access tables.

No, the rows are not filtered, although they are a number of subtotals

When the code does not run successfully, it breaks on the first sheet of the For/Next loop.

 

[rootdown42]

Well in case anyone else stumbles across this problem this article helped me fix the unqualified object issue so that I no longer received errors.

http://support.microsoft.com/default.aspx?kbid=178510

 

[Ragnar1211]

So what did you change to make it work?

I read the KB article and it looks like you need to make sure you reference everything specifically, which it looks like you already have.

 

[rootdown42]

Yeah, basically the problem was exactly as it was described in the article. By opening new workbooks all the time, the unqualified methods were referencing the wrong application object, causing the errors when I tried to do a paste or something like that. While that's what I figured the problem was, I was unfamiliar with creating application objects, and that's what I was looking for direction on. The second MSKB article helped me figure it out. So essentially I added

Dim xlApp As Excel.Application

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

and then qualified my workbook and worksheet objects like

Set wbORep = xlApp.Workbooks.Open(strFileName)
xlApp.Goto reference:=wsORep.Range("A1")