This is my first post on MrExcel...hope you can help me?
My task is to open several embedded workbooks from a data workbook (vTempWorkbook) and copy some information from each embedded workbook to the main workbook (vMainWorkbook).
The code below works fine if run in debug mode (F8). However Im having an error when the code selects the sheet "Test Plan" when run outside debug mode. It is as if the embedded worksheet is not really activated??? A debug just before the sheet selection returns the name of the embedded workbook when run in debug mode and the name of the data workbook when outside debug mode.
Anyone?
Regards
Kasper
My task is to open several embedded workbooks from a data workbook (vTempWorkbook) and copy some information from each embedded workbook to the main workbook (vMainWorkbook).
The code below works fine if run in debug mode (F8). However Im having an error when the code selects the sheet "Test Plan" when run outside debug mode. It is as if the embedded worksheet is not really activated??? A debug just before the sheet selection returns the name of the embedded workbook when run in debug mode and the name of the data workbook when outside debug mode.
Anyone?
Code:
Sub Macro1()
vMainWorkbook = ActiveWorkbook.Name
vTempWorkbook = "DataWorkbook.xls"
For i = 1 To ActiveSheet.OLEObjects.Count
Windows(vTempWorkbook).Activate
'Open embedded workbook and copy range
ActiveSheet.OLEObjects(i).Verb xlOpen
Windows("Worksheet in " & vTempWorkbook).Activate
Debug.Print ActiveWorkbook.Name
Sheets("Test Plan").Select
Range("A7:F18").Copy
'Paste range in main workbook
Windows(vMainWorkbook).Activate
Sheets("KeyControlsTest").Range("A" & ((i - 1) * 14) + 1).Select
ActiveSheet.Paste
'Close embedded workbook
Windows("Worksheet in " & vTempWorkbook).Close
Next i
'Sort and filter on data copied (pending)
End Sub
Regards
Kasper