Search code examples
excelvbacopyworksheet

Run-time error 424 on Excel VBA "Object required" for very basic worksheet copy function

Here is a snippet of the macro that I am having a problem with, the error message appears just as it does in the larger macro it is a part of :

Sub copyWorksheet()

Dim ws1 As Worksheet
Set ws1 = Worksheets("Manifest Blank").Copy(After:=Worksheets(Worksheets.Count))

End Sub

This seems to work as intended, it creates a duplicate of my worksheet called "Manifest Blank" but then shows an error message : Run-time error 424 on Excel VBA "Object required"

I have tried :

  • renaming evertyhing "sheets" instead of "worksheets"
  • changing "worksheets.count" to an actual number or a named worksheet
  • changing the named worksheet to an actual number or a named worksheet

All of these result in the intended behaviour but all still show the run-time error 424.

Solution

  • copy is a method of worksheet. It doesn't return the worksheet it self.

    But the new sheet will always be the ActiveSheet

    Sub copyWorksheet()

Worksheets("Manifest Blank").Copy After:=Worksheets(Worksheets.Count)

Dim ws1 As Worksheet
Set ws1 = ActiveSheet
End Sub