Unable to set a variable value

[rmccafferty]

This is an excel macro and I just cannot get one variable to set a value. I have rem'd out most of the lines of the below trying to eliminate any other issues that could cause the problem. I am trying to set two variables as worksheets (Ws1 and Ws2) to excel files. And eventually I want to copy data from one to the other.

The Ws1 variable works just fine with everything else Rem's out.

When I rem out everything but the Dim and Set for Ws2, I get an error message whatever I do. I eventually set up a file named A, with a Sheet named A. And every time I run the code, I get an error with the "set Ws2" line highlighted. The lines of code break in the below due to the short width of the window. But the Set Ws1 that is 3 lines long here is only 1 line long in my code. I believe the only two lines not rem'd out are the Dim and Set commands for Ws2.

'Dim Ws1 As Worksheet
Dim Ws2 As Worksheet


' Set Ws1 = Workbooks("tAVRHeartTeamEvaluation_Data_scsv_041011.xls").Sheets("tAVRHeartTeamEvaluation_Data")
Set Ws2 = Workbooks("A.xls").Sheets("A")
' Set Ws2 = Workbooks("Book2.xls").Sheets("Sheet1")

If anyone can tell me what is wrong, I would greatly appreciate it.

 

[dk]

Hi

What is the error message? Is it "subscript out of range"?

Do your sheets represent actual worksheets or are they Chart sheets?

What happens if you use this code:

Sub Test()
Dim oSheet As Object

Set oSheet = Workbooks("book2.xls").Sheets("Sheet1")

MsgBox TypeName(oSheet)

End Sub

DK

 

[rmccafferty]

Thank you for the help. I really need to learn to use message boxes. I use them a lot in Access queries, but have never attempted it in Excel.