1

I got an error 424: Object Required.

It's a very simple script, I can't see where I'm going wrong.

The strange thing is, this worked many times before. I am only getting these errors today.

I rename sheet one as "All Data". Then add another sheet and rename it "List".

The error happens when I try to activate the "List" sheet. Object Required. I guess it can't detect the new sheet? Where am I going wrong? As I said this worked 100 times before.

    Sheets(1).Select
    Sheets(1).Name = "All Data"

    Sheets.Add After:=ActiveSheet
    Sheets(2).Select
    Sheets(2).Name = "List"
    List.Activate ' error happens here

I have also tried List.Select instead of Activate. Same error.

braX
  • 11,506
  • 5
  • 20
  • 33
SCool
  • 3,104
  • 4
  • 21
  • 49

1 Answers1

3

If List is not the VBA name of a sheet it has to be defined:

Dim List As Worksheet
Set List = ThisWorkbook.Worksheets("List")

or make sure the VBA name of that sheet is actually changed to List in the properties window of the VBA editor.

  • List.Activate activates a sheet which VBA name is List
  • Worksheets("List").Activate activates a sheet which tab name is List

note that these 2 naming systems are completely independent.

You might benefit from reading How to avoid using Select in Excel VBA.

Worksheet(1).Name = "All Data"
Worksheet.Add(After:=Worksheet("All Data")).Name = "List"
Worksheet("List").Activate ' don't use activate! see link above

or

Worksheet(1).Name = "All Data"

Dim List As Worksheet
Set List = Worksheet.Add(After:=Worksheet("All Data"))
List.Name = "List"
List.Activate ' don't use activate! see link above

Don't mix Sheets with Worksheets they count differently. Sheets contains all types of sheets like worksheets, chart sheets, etc. but Worksheets only contains worksheets.

If you have 2 worksheets and 1 chart sheet:

  • Worksheets.Count is 2 but
  • Sheets.Count is 3.
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73