Excel VBA Run-time error '438': Object doesn't support this property or method

Question

I'm running this code in VBA and I can't figure out where my error is. I keep getting the error message

"Run-time error '438': Object doesn't support this property or method".

I've searched the internet for solutions but haven't been able to find anything that fixed my problem.

 Sub FinalCleanUp()
    Dim wkbk As Workbook
    Dim wksht As Worksheet
    Dim DataSheet As Worksheet
    Dim sheetName As String

    sheetName = "Data"
    Set wkbk = ActiveWorkbook

    'Delete consolidated data sheet if it already exists
    For Each wksht In wkbk.Sheets
        If wksht.Name = sheetName Then
            wkbk.Sheets(sheetName).Delete
            Exit For
        End If
    Next wksht

    'Create new sheet for consolidated data
    wkbk.Sheets.Add Before:=wkbk.Sheets(1)
    Set DataSheet = ActiveSheet
    ActiveSheet.Name = sheetName

    'Step through each sheet and copy data to consolidated data sheet
    'ERROR IS SOMEWHERE BELOW HERE
    For Each wksht In wkbk.Sheets
        If wksht.Name <> sheetName Then
            wksht.Activate
            Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)).Copy
            If wksht = wkbk.Sheets(2) Then
                DataSheet.Activate
                Cells(ActiveSheet.UsedRange.Rows.Count, 1).Select
            Else
                DataSheet.Activate
                Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).Select
            End If
            ActiveSheet.Paste
        End If
    Next wksht

End Sub

This code is supposed to take data from multiple worksheets and consolidate it into one worksheet. As previously stated, I get a run-time error and no output when I run the macro.

What are you trying to do with `If wksht = wkbk.Sheets(2) Then`? — BigBen, Jun 21 '19 at 15:43
Some of my code appears outside the gray box. Sorry about that, this is my first time asking a question on StackOverflow! — Amie Beckwith, Jun 21 '19 at 15:43
@BigBen When I paste my first block of data into the data sheet, I don't want it to skip a row, but for every sheet after that, I need it to paste starting at a row below the previous data so that it doesn't cut off my last row of data from the previous sheet. Does that make sense? — Amie Beckwith, Jun 21 '19 at 15:46
You can't directly compare one sheet to another. You can compare properties of a worksheet. But I think there's a way to clean this up to avoid using that `If` entirely. — BigBen, Jun 21 '19 at 15:48
@TylerH I'm pretty new to this and I'm not sure how to tell what line the error is on, but I think it's somewhere within my If Else statement because the data is copying from sheet 2 but not pasting into the data sheet. — Amie Beckwith, Jun 21 '19 at 15:50
Try 'wksht.Name = wkbk.Sheets(2).Name' instead of If wksht = wkbk.Sheets(2)' — Peicong Chen, Jun 21 '19 at 15:51
@PeicongChen You should post that as an answer so OP can accept it. — TylerH, Jun 21 '19 at 16:07
@AmieBeckwith For VBA, you can step through your code in the VBA Editor by pressing the F8 key. If you have a general idea of where your code is breaking, but don't know exactly what line, you can find out one of two ways: run it with the VBA Editor window open (it will stop on the line that throws the error), or put a breakpoint (click the grey column to the left of where the code is to add a red dot) somewhere right before the section you know is throwing the error, then run the entire thing from the top. Once the code pauses at the breakpoint, press F8 to step into the code, step by step. — TylerH, Jun 21 '19 at 16:10
[How to avoid Select and Activate](https://stackoverflow.com/q/10714251/1188513) might be a useful read. If you qualified these `Range` and `Cells` calls with a `Worksheet` object, you wouldn't need to `Activate` any sheets. — Mathieu Guindon, Jun 21 '19 at 16:19

Mathieu Guindon · Accepted Answer · 2019-06-21T16:29:55.943

The error is here:

If wksht = wkbk.Sheets(2) Then

The Worksheet class doesn't have a default property, so you can't let-coerce that object; VBA is trying to invoke the default member of Worksheet to satisfy the comparison operator, but there's no such member, so error 438 is raised.

I think what you're trying to do is to validate whether wksht is wkbk.Sheets(2). You need the Is operator for reference equality:

If wksht Is wkbk.Sheets(2) Then

Note that this is different from comparing the .Name of the sheets: here we're comparing object references.

That said, I'd buy a vowel or two here, it's too easy to make a typo typing those... Make sure Option Explicit is specified!

Addendum

wkbk.Sheets.Add Before:=wkbk.Sheets(1)
Set DataSheet = ActiveSheet

Worksheets.Add returns the added worksheet object, so you can do this instead, and not rely on the side-effect of the added sheet now being the ActiveSheet:

Set DataSheet = wkbk.Sheets.Add(Before:=wkbk.Sheets(1))

Excel VBA Run-time error '438': Object doesn't support this property or method

1 Answers1

Addendum