Excel Workbook and Worksheet objects

[drkestrel]

Hey could anyone tell me what's wrong with the following piece of code

    Set myWorkBook = ActiveWorkbook
    Dim myWorkSheet1 As Worksheet
    Dim myWorkSheet3 As Worksheet
    
    MsgBox myWorkBook.Name
    myWorkSheet1 = myWorkBook.Worksheets(1)
    myWorkSheet3 = myWorkBook.Worksheets(3)

It gives me "Run time error 91 Object variable or with block not set" on

    myWorkSheet1 = myWorkBook.Worksheets(1)

Worksheets(1) work, but the reason I am assigning it to myWorkSheet1 is that the code subsequently would open another spreadsheet (i.e. worksheets(1) contained in another workbook), so Worksheets(1) won't work as desired.

Anywaym what's wrong with myWorkBook.Worksheets(1)??

 

[DarkSun]

You didn't use Set.

Dim myWorkBook As Workbook
Set myWorkBook = ActiveWorkbook
Dim myWorkSheet1 As Worksheet
Dim myWorkSheet3 As Worksheet

MsgBox myWorkBook.Name

Set myWorkSheet1 = myWorkBook.Worksheets(1)
Set myWorkSheet3 = myWorkBook.Worksheets(3)