Excel Error in VBA " Run-time error '424': Object required at SET statement

Question

I'm getting Run-time error '424' at the last line of the code and cannot undertand why. Any help please.

Dim sourceSh As Worksheet
Dim destSh As Worksheet
Dim FileName As String
Dim destWk As Workbook
Dim sourceWk As Workbook

FileName = "Data.xlsx"

Set destWk = Workbooks.Open(FileName)
Set sourceWk = DataEntry
Set sourceSh = sourceWk.Sheets(Data_Entry.Name)  'why here I'm not getting error !!
Set destSh = destWk.Sheets(Employees.Name)      'here I'm getting Run-time error 424.

I have cotroled speel check and everything looks fine.

What is `DataEntry` and what is `Data_Entry` and what is `Employees` and do you use `Option Explicit`? And `FileName = "Data.xlsx"` needs a path. You cannot open a file `Workbooks.Open(FileName)` without specifying its path. — Pᴇʜ, Nov 11 '20 at 14:01
Data_Entry is a Code name of the sheet, where DataEntry is code name of the workbook (insted of ThisWorkbook), yes I have Option Explicit. FileName has a path... the file is oppening. the problem is on the last SET destSh......... — Turan Aydin, Nov 11 '20 at 14:56
@Turan Aydin: If "Data_Entry is a Code name of the sheet" then why do you assign a worksheet object "DataEntry" to "sourceWk", which is a workbook object?! Are you sure? — , Nov 11 '20 at 15:05
DataEntry is a Workbook, so source book is "Set sourceWk = DataEntry", when I debug.Print sourceWk , the result is correct. when I debug.Print sourceSh result also is correct — Turan Aydin, Nov 11 '20 at 15:14
@Turan Aydin: OK, now that we know that "DataEntry is a Workbook" and not a worksheet, let's examine the "Employees" object: What is it? — , Nov 11 '20 at 15:28
when I compile the sub , I'm getting error "Varable not defined" for "Employees" in "Set destSh = destWk.Sheets(Employees.Name)" — Turan Aydin, Nov 11 '20 at 15:32
@Turan Aydin: Absolutely! So, what SHOULD IT BE, this "Employees"? — , Nov 11 '20 at 15:34
If Employees is a worksheet code name that is in another workbook you would need to add the workbook reference in otherwise it's looking at the activeworkbook. — Warcupine, Nov 11 '20 at 15:40
Dim FileName As String: FileName = "path/Data.xlsx" MsgBox FileName 'is correct Dim sourceWb As Workbook: Set sourceWb = DataEntry 'def source workbook MsgBox sourceWb.Name 'is correct Dim sourceSh As Worksheet: Set sourceSh = sourceWb.Sheets(Data_Entry.Name) 'def source sheet MsgBox sourceSh.Name ' is correct Dim destWb As Workbook: Set destWb = Workbooks.Open(FileName) 'def and open destination workbook MsgBox destWb.Name 'is correct Dim destSh As Worksheet: Set destSh = destWb.Sheets(Employees.Name) 'dest sheet - now : error 9 — Turan Aydin, Nov 11 '20 at 17:33
The line `Set destSh = destWb.Sheets(Employees.Name)` cannot work. It could only work if `Employees` were in `ThisWorkbook`, the workbook containing this code. But it would be overcomplicated because you could just do `Set destSh = Employees`. Read the comments in my posted function. — VBasic2008, Nov 11 '20 at 17:36

score 0 · Answer 1 · answered Nov 11 '20 at 15:48

0

You are setting a worksheet object "destSh" by assigning to it some worksheet from the workbook "destWk".

If "Employees" is an existing worksheet's code name then there should be no error (BTW, just

Set destSh = destWk.Employees

would suffice in this case).

If "Employees" is a worksheet name (tab caption) then you should have written

Set destSh = destWk.Sheets("Employees")

Since you did not, it is likely not the case...

So, the only conclusion possible is that neither version of "Employees" exists in "destWk".

answered Nov 11 '20 at 15:48

It is easy to test your first `Set` statement. Open two new workbooks. In the first (`Book1`) insert a module. Create a sub and place the following two lines in it: `Dim destSh As Worksheet` and `Set destSh = Workbooks("Book2").Sheet1`, or just use `Debug.Print Workbooks("Book2").Sheet1.Name` and see `Run-time error '438'` occurring. – VBasic2008 Nov 11 '20 at 17:11
@VBasic2008: You are absolutely correct: it is easy to test, the way you describe or any other. I hope Turan Aydin has done that by now. – Nov 11 '20 at 17:45

VBasic2008 · Answer 2 · 2020-11-11T16:55:06.690

Define Worksheet by Code Name

I would suggest that you just use Data_Entry in ThisWorkbook; there is no need to assign it to a variable (SourceSh) when it is already defined.

The Code

Option Explicit

Sub wsByCodename()

    Dim sourceSh As Worksheet
    Dim destSh As Worksheet
    Dim FileName As String
    Dim destWk As Workbook
    
    ' The following should contain the 'complete' path, e.g. "C:\Test\Data.xlsx".
    FileName = "Data.xlsx"
    Set destWk = Workbooks.Open(FileName)
    
    Set destSh = defineWorksheetByCodeName(destWk, "Employees")
    Set sourceSh = Data_Entry

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      In a specified workbook, NOT containing this code, returns
'               the worksheet (object) which is specified by its code name.
' Remarks:      If it is expected that a worksheet might get renamed
'               or moved to another position, it is best to refer to it
'               by its code name. In 'ThisWorkbook', the workbook containing
'               this code, you will use just e.g. 'Sheet1'.
'               But in another workbook, you could use this function,
'               because you CANNOT use e.g. 'ActiveWorkbook.Sheet1'.
' Example:      Const WorksheetCodeName As String = "Sheet1"
'               Dim wb As Workbook: Set wb = ActiveWorkbook
'               Dim ws As Worksheet
'               Set ws = defineWorksheetByCodeName(wb, WorksheetCodeName)
'               If ws Is Nothing Then Exit Sub ' or whatever.
'               ' Continue with code...
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function defineWorksheetByCodeName(Book As Workbook, _
                                   ByVal WorksheetCodeName As String) _
         As Worksheet
    Dim ws As Worksheet
    For Each ws In Book.Worksheets
        If StrComp(ws.CodeName, WorksheetCodeName, vbTextCompare) = 0 Then
            Set defineWorksheetByCodeName = ws
            Exit For
        End If
    Next ws
End Function

yes you are right the codenames are not qualifiable...what a pity. Thank you very much for the help and for the Function... I just wondering is it possible to do it with Class Module... — Turan Aydin, Nov 12 '20 at 07:43

Excel Error in VBA " Run-time error '424': Object required at SET statement

2 Answers2

Define Worksheet by Code Name