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:)