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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
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,836
Office Version
  1. 2019
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,836
Office Version
  1. 2019
Platform
  1. Windows
No problem - and to Norie (I wasn't really adding much ... :) )
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top