I've downloaded and tried the zipped examples, which work fine. However, attempting to use the same logic does not work in my workbooks...
Trying to figure out what is going on, I tried a simple debug routine:
Public Sub WorksheetsReport()
Dim i As Integer
For i = 1 To ThisWorkbook.Worksheets.Count
MsgBox ThisWorkbook.Worksheets(i).Name & " has index = " & ThisWorkbook.Worksheets(i).Index
Next i
End Sub
But this causes:
"Run-time error '-2147418113 (8000ffff)':
Automation error
Catastrophic failure"
when control hits the "for i = ..." line. I get the same error in the GetDataFromClosedWorkbook() routine when it reaches the reference to "ThisWorkbook" too.
For reference, here's my version of that routine:
Public Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Dim wks As Worksheet
Dim path As String
Application.ScreenUpdating = False ' turn off the screen updating
path = "C:\Documents and Settings\senft100\My Documents\blakieto\jobCosting\TD Forecast\"
Set wb = Workbooks.Open(path & "AnimLayTDForecast.xls", True, True) ' open the source workbook, read only
With ThisWorkbook.Worksheets("AnimLay Schedule")
' read data from source workbook
.Range("Staff").Formula = wb.Worksheets("AnimLay Schedule").Range("Staff").Formula
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub
which get that same "Catastrophic failure" when control reaches the 'With ThisWorkbook.Worksheets("AnimLay Schedule")' line.
Any ideas? The name of the sheet is "AnimLay Schedule". I've verified that multiple times now... In the Ranges() calls, I'm referencing named ranges; which should be OK, since I have named "Staff" ranges in both workbooks...
-Blakieto