Switching worksheets in another workbook

[doubtfire]

I am trying to retrieve data from worksheets of another workbook to the current "this" workbook.

[vba]
Dim ThisWB As Workbook
Dim ThisWS As Worksheet

Dim AnotherWB As Workbook
Dim AnotherWS As Worksheet

Set ThisWB = ThisWorkbook
Set ThisWS = ThisWB.Worksheets("TargetSheet")

Set AnotherWB = Workbooks.Open(filename:="C:\test1.xlsm", IgnoreReadOnlyRecommended:=True)

Set AnotherWS = AnotherWB.Worksheets("SheetAA")
ThisWS.Range("A1").value = AnotherWS.Range("A1").value

'****This is where system crashes
Set AnotherWS = AnotherWB.Worksheets("SheetBB")
ThisWS.Range("A2").value = AnotherWS.Range("A1").value

Set AnotherWS = AnotherWB.Worksheets("SheetCC")
ThisWS.Range("A3").value = AnotherWS.Range("A1").value

[/vba]

Any ideas? I have tried to use the whole qualified Workbook.Worksheet.Range and it is still crashed.
Thanks.

[Bob Phillips]

Does this give you the same problem?

[VBA]Dim ThisWB As Workbook
Dim ThisWS As Worksheet

Dim AnotherWB As Workbook
Dim AnotherWS As Worksheet

Set ThisWS = ThisWorkbook.Worksheets("TargetSheet")

Set AnotherWB = Workbooks.Open(Filename:="C:\test1.xlsm", IgnoreReadOnlyRecommended:=True)

AnotherWB.Worksheets("SheetAA").Range("A1").Copy ThisWS.Range("A1")

AnotherWB.Worksheets("SheetBB").Range("A1").Copy ThisWS.Range("A2")

AnotherWB.Worksheets("SheetCC").Range("A1").Copy ThisWS.Range("A3")
[/VBA]

[doubtfire]

The Lord,

Thank you for the suggestion.
Actually the code is a loop to read a range of this worksheet into a variant.
If you could show me with the application of your suggestion, it would be great.
And I might have found the problem, which is one of the worksheet is "Sheet_Name1" (where it crashes) has an underscore. When I replaced it with a "-", it works.
Is there such a limitation?
Gracia!

[snb]

You can use:

[vba]
Sub snb()
With getobject("C:\test1.xlsm")
for j=1 to 3
thisworkbook.sheets("Targetsheet").cells(j,1)=.sheets(choose(j,"AA","BB","C C")).cells(1).value
next
.close False
end with
end sub
[/vba]
Assuming
- the workbook that contains this code also has a sheet named 'targetsheet'
- the workbook C:\test1.xlsm" contains the sheets 'AA', 'BB' and 'CC'

[doubtfire]

Thanks.