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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,769
Office Version
365
Platform
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, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
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, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
No problem - and to Norie (I wasn't really adding much ... :) )
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,107,005
Messages
5,514,755
Members
409,019
Latest member
SaKoya

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top