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

Question

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.

There is no return on `.copy` – Warcupine Jan 27 '23 at 13:57 — Warcupine, Jan 27 '23 at 13:57
@Warcupine - well technically `.Copy` returns a `Boolean`. – BigBen Jan 27 '23 at 14:13 — BigBen, Jan 27 '23 at 14:13
@BigBen That would make sense. – Warcupine Jan 27 '23 at 14:15 — Warcupine, Jan 27 '23 at 14:15

score 1 · Accepted Answer · answered Jan 27 '23 at 13:59

1

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

answered Jan 27 '23 at 13:59

Ike

9,580
4
13
29

For confidence, after copy, Set ws1=Worksheets(Worksheets.Count) – ValNik Jan 27 '23 at 14:07
I knew it would be something like this, but I am still early in my learning.Thank you! – HopelessBrexitVictim Jan 27 '23 at 14:31

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

1 Answers1