I've been beating my head against the wall on this one for a couple weeks now and have finally decided (I'm stubborn, I am) that I'm not going to resolve this one myself. Currently, the specific error is "Method 'Insert" of object 'Range" failed" although it has also manifested as "Automation error The object invoked has disconnected from its clients." While I've searched the various forums, none of the reported instances seem to match this case.
I have a fairly complex workbook which is used by project managers in my company to estimate the initial project costs and to track project financial progress. The Workbook has evolved over many years and through more hands than mine for the macros provided to manipulate the spreadsheet. In its current incarnation, there are 20 worksheets, some of which are routinely hidden from the user. We are finally creating a comprehensive regression test for this workbook and this error was discovered during testing prior to deployment.
To use the workbook, the initial estimate is entered using either the Worksheet Estimate sheet or the MPP Estimate sheet. Various project parameters are entered on one of these sheets including Region & Currency. The failing case is to change the Region & Currency (cell H3) on the Worksheet Estimate sheet, select the Project Overview sheet and click the "Add PCR" button at the top. This seems to fail every time. Another failing case is to change the Estimation Method (cell B14) on the Project Overview sheet from "Worksheet" to "MPP" and click the "Add PCR" button. Both the Region & Currency and Estimation Method are selected from a drop down list using data validation and run no code, so it's extremely difficult to think of any reason this should affect the operation of the macro, and it also fails when a value in a cell without data validation is changed. Passing cases include:
I wanted to attach the spreadsheet, but can't find a button to do that, so here's the failing code. If you want to see the spreadsheet, I can email that.
There's more to the macro, but it's failing at the line in red text, although that command works in other cases. The really interesting thing is that when I enter debug and look at the worksheet, the command has already been executed when it fails. The new template has been added to the sheet, but it's still highlighting this command as the failure.
Any suggestions will be welcomed!
Regards,
Pat Klaus
I have a fairly complex workbook which is used by project managers in my company to estimate the initial project costs and to track project financial progress. The Workbook has evolved over many years and through more hands than mine for the macros provided to manipulate the spreadsheet. In its current incarnation, there are 20 worksheets, some of which are routinely hidden from the user. We are finally creating a comprehensive regression test for this workbook and this error was discovered during testing prior to deployment.
To use the workbook, the initial estimate is entered using either the Worksheet Estimate sheet or the MPP Estimate sheet. Various project parameters are entered on one of these sheets including Region & Currency. The failing case is to change the Region & Currency (cell H3) on the Worksheet Estimate sheet, select the Project Overview sheet and click the "Add PCR" button at the top. This seems to fail every time. Another failing case is to change the Estimation Method (cell B14) on the Project Overview sheet from "Worksheet" to "MPP" and click the "Add PCR" button. Both the Region & Currency and Estimation Method are selected from a drop down list using data validation and run no code, so it's extremely difficult to think of any reason this should affect the operation of the macro, and it also fails when a value in a cell without data validation is changed. Passing cases include:
- Open the workbook, immediately select the Project Overview sheet and click the "Add PCR" button. This works reliably.
- After changing the Region & Currency on the Worksheet Estimate sheet, use the macro to Add Task Rows, then select Project Overview and click Add PCR.
- After changing the Estimation Method on the Project Overview sheet, select the MPP Estimate sheet and use the button to Insert Estimated Labor Rows, then select Project Overview and click Add PCR.
I wanted to attach the spreadsheet, but can't find a button to do that, so here's the failing code. If you want to see the spreadsheet, I can email that.
Code:
Public Sub AddPCR()
'
' Adds a PCR to the Project Overview sheet, the Labor Roll-up sheet and the PCR Summary sheet.
'
Dim curCol As Integer
Dim colStr As String
Dim ColumnRange As String
Dim doneProcessing As Boolean
Dim Visible1 As Boolean
' First, turn off all screen updates and automatic calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' Make hidden sheets visible
Sheets("Tables").Visible = True
Sheets("Rollup_Tables").Visible = True
Visible1 = Sheets("Labor Roll-up").Visible 'save the visible status of the sheet to be restored at end of sub
Sheets("Labor Roll-up").Visible = True
' Copy the calculation PCR template and paste into the Labor Roll-up sheet
Sheets("Rollup_Tables").Select
Range("CalculationPCRTemplate").Copy
Sheets("Labor Roll-up").Select
curCol = Range("Last_PCR_Calc_Col").Column
colStr = ConvertColumnNumberToLetter(curCol)
ColumnRange = colStr + ":" + colStr
[COLOR=#ff0000] Range(ColumnRange).Insert Shift:=xlToRight
[/COLOR] Application.CutCopyMode = False
...
There's more to the macro, but it's failing at the line in red text, although that command works in other cases. The really interesting thing is that when I enter debug and look at the worksheet, the command has already been executed when it fails. The new template has been added to the sheet, but it's still highlighting this command as the failure.
Any suggestions will be welcomed!
Regards,
Pat Klaus