Open and copy data from embedded workbooks

[Kjuel]

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?

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

 

[Kjuel]

Got it!

Replaced:

Windows("Worksheet in " & vTempWorkbook).Activate 
Debug.Print ActiveWorkbook.Name 
Sheets("Test Plan").Select 
Range("A7:F18").Copy

With:

Set wb1 = Workbooks("Worksheet in " & vTempWorkbook)
wb1.Sheets("Test Plan").Range("A7:F18").Copy

Thanks for dropping by
: )