Subscript out of range when jumping workbooks

[qjd2004]

Hiya,

trying to jump from 1 workbook to another, starting in book1 jumping to book2 to copy a range from sheet "NUTS" and back to book1 to paste it into sheet "EUP."

I get subscript out of range. here's my code:

Sub CopyTheHeader()

    Application.Windows("book2.xls").Worksheets("NUTS").Activate   'fails here
    Range("A1:Y10").Select
    Selection.Copy
    Application.Windows("book1.xls").Worksheets(2).Activate  'will fail here
    Range("D1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
End Sub

Can somebody tell me what I'm doing wrong?

 

[PHV]

Use the Workbooks collection instead of the Windows one.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[qjd2004]

Hi,

thanks for your reply PHV. I've tried your suggestion but I'm having a problem with it still.

Sub CopyTheHeader()
    
    Application.Workbooks("Book2.xls").Worksheets(9).Activate
    Range("A1:Y10").Select
    Selection.Copy
    Application.Workbooks("Book1.xls").Worksheets(2).Activate
    Range("D1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
 
End Sub

Am I getting the syntax wrong?

 

[Enkay62]

Replace yor first string (Wrong)
"Application.Windows("book2.xls").Worksheets("NUTS").Activate"

With these two strings

Windows("book2.xls").Activate
Worksheets("NUTS").Select

and it should work.
Hope this helps.
Bye
Nick

 

[PHV]

qjd2004, which problem (exact and complete error message) and where (exact line of code highlighted) ?

 

[qjd2004]

Hi Nick,

that's great! Thanks for your help!