Selecting workbook to run macro

[Rajesh Kr Joshi]

Hi ,

What is the best method to select a workbook to run macro? I have tried 2 different codes (Below), but both of them sometime gives error: 'Subscription out of Range'. Also this seems an intermittent problem as sometime it works fine.

Workbooks("Mydata").Activate

Windows("Mydata").Activate

Thanks
Rajesh

[Admin]

Hi

No need to activate. Try to explicitly dim the variables. Try something like

Dim wbkSource   As Workbook
Dim wbkTarget   As Workbook
Dim wksSource   As Worksheet
Dim wksTarget   As Worksheet
Dim rngTarget   As Range


Set wbkSource = ThisWorkbook
Set wbkTarget = Workbooks("Mydata") 'The 'Mydata' workbook must be open

Set wksSource = wbkSource.Worksheets("Sheet1")
Set wksTarget = wbkTarget.Worksheets("Data")
Set rngTarget = wksTarget.Range("A1")

wksSource.Range("A1:D10").Copy rngTarget

[Rajesh Kr Joshi]

Thanks Admin. It worked