Selecting workbook to run macro

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.

Code:

---

Workbooks("Mydata").Activate

---

Code:

---

Windows("Mydata").Activate

---

Thanks
Rajesh

Hi

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

Code:

---

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

---

Thanks Admin. It worked:)