Runtime error 438 for worksheet.select

[shantanu]

I am trying to use the following code to open a worksheet but get the runtime error 438 for line "DestBook.Anlysheet.Select".

Cannot understand where I am going wrong. Please help.

Dim strFName As String
    Dim DestBook As Workbook, SourceBook As Workbook
    Dim DestCell As Range, TinR As Range
    Dim RetVal As Boolean
    Dim TINdataIndex As Long, AnlysheetIndex As Long
    Dim Anlysheet As Worksheet
    
    'code starts from here'
    Set DestBook = ActiveWorkbook
    
        
    'To open the source workbook'
    For Each Anlysheet In DestBook.Worksheets(Array("Y02A", "Y01A", "Y02B", "Y01B"))
    
    DestBook.Anlysheet.Select
    Range("D10").Select
    Set DestCell = ActiveCell
    strFName = Anlysheet(AnlysheetIndex).Range("B2").Value
    'this variable contains the workbook name and path
    If FileExists(strFName) Then
    'does it exist?
        If Not BookOpen(Dir(strFName)) Then Workbooks.Open Filename:=strFName
        'if its not already open, open it
    Else
        MsgBox "The file does not exist!"
    End If
    
    Set SourceBook = ActiveWorkbook

 

[Norie]

You could try removing the workbook name DestBook from that line of code.

You might also want to consider not using Select - it's shouldn't be needed.

    Set DestCell = Anlysheet.Range("D10")

Apart from that all I can suggest is you post an explanation, in words, what the code is meant to do - it's pretty confusing.

For example what is Anlysheet(AnlysheetIndex)? AnlysheetIndex doesn't even seem to be given a value anywhere.

 

[xenou]

error 438 is a new one to me - you get some bonus points. Norie is right on the money - the syntax isn't right. you've already dimmed anlysheet as a worksheet and you can use it "as is".

[COLOR="Navy"]Dim[/COLOR] strFName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] DestBook [COLOR="Navy"]As[/COLOR] Workbook, SourceBook [COLOR="Navy"]As[/COLOR] Workbook
    [COLOR="Navy"]Dim[/COLOR] DestCell [COLOR="Navy"]As[/COLOR] Range, TinR [COLOR="Navy"]As[/COLOR] Range
    [COLOR="Navy"]Dim[/COLOR] RetVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] TINdataIndex [COLOR="Navy"]As[/COLOR] Long, AnlysheetIndex [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] Anlysheet [COLOR="Navy"]As[/COLOR] Worksheet
    
    [COLOR="SeaGreen"]'code starts from here'[/COLOR]
    [COLOR="Navy"]Set[/COLOR] DestBook = ActiveWorkbook
    
        
    [COLOR="SeaGreen"]'To open the source workbook'[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Anlysheet [COLOR="Navy"]In[/COLOR] DestBook.Worksheets(Array("Y02A", "Y01A", "Y02B", "Y01B"))
    
        [COLOR="Blue"][B]Anlysheet.Select[/B][/COLOR]
        Range("D10").Select
        [COLOR="Navy"]Set[/COLOR] DestCell = ActiveCell
        [COLOR="Blue"][B]strFName = Anlysheet.Range("B2").Value[/B][/COLOR]
        [COLOR="SeaGreen"]'this variable contains the workbook name and path[/COLOR]
        
        [COLOR="Navy"]If[/COLOR] FileExists(strFName) [COLOR="Navy"]Then[/COLOR]
        [COLOR="SeaGreen"]'does it exist?[/COLOR]
            [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] BookOpen(Dir(strFName)) [COLOR="Navy"]Then[/COLOR] Workbooks.Open Filename:=strFName
            [COLOR="SeaGreen"]'if its not already open, open it[/COLOR]
        [COLOR="Navy"]Else[/COLOR]
            MsgBox "The file does not exist!"
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
        [COLOR="Navy"]Set[/COLOR] SourceBook = ActiveWorkbook
           
        [COLOR="SeaGreen"]'//More code...?[/COLOR]
        
    [COLOR="Navy"]Next[/COLOR] Anlysheet

You may soon get a lecture about not using Select in your code ...

 

[shantanu]

Thanks xenou. That solved it.

 

[xenou]

No problem - and to Norie (I wasn't really adding much ... )