Runtime error 438 for worksheet.select

shantanu

New Member
Joined
Jul 30, 2010
Messages
22
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.


Code:
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
    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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,690
Office Version
  1. 2013
Platform
  1. Windows
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".


Code:
[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 ... ;)
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,690
Office Version
  1. 2013
Platform
  1. Windows
No problem - and to Norie (I wasn't really adding much ... :) )
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,622
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top