PDA

View Full Version : Switching worksheets in another workbook



doubtfire
08-30-2012, 02:37 PM
I am trying to retrieve data from worksheets of another workbook to the current "this" workbook.


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



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

Bob Phillips
08-30-2012, 03:38 PM
Does this give you the same problem?

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

doubtfire
08-30-2012, 04:09 PM
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
09-01-2012, 04:17 AM
You can use:


Sub snb()
With getobject("C:\test1.xlsm")
for j=1 to 3
thisworkbook.sheets("Targetsheet").cells(j,1)=.sheets(choose(j,"AA","BB","CC")).cells(1).value
next
.close False
end with
end sub

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
09-01-2012, 06:16 AM
Thanks.:clap: