MS Excel vba Run-Time Error '1004'

Question

MS Excel vba Run-Time Error '1004'

John and Carol Smith 0

In MS Excel vba why do I get a run-time error '1004': Paste method of worksheet Class failed, It has been OK but it has just stared failing. The section of code I am running is:

s1 = Worksheets.Count

'

Range("K1:L48").Select

Selection.NumberFormat = "0.000"

Range("K48").Activate

'

Selection.Copy

Worksheets(s1 - 1).Select

ActiveSheet.Paste

It fails at the "ActiveSheet.Paste"

0 comments

2 answers

Your answer

Answer 1

EmilyS726 266.9K Independent Advisor

This can be related to a known issue due to a recent update. See article. This issue is causing problem when copying and pasting.
https://support.microsoft.com/servicing/office/hotfix/excel/5002914

I can provide you with a command to roll back to the last working version, but in order for me to get you the correct command, I need to know your license type.

For example, is this Office Home or LTSC/Standard/Pro Plus, and what year?

You can go to Files > Accounts in Excel app and see what the product name is there.

0 comments

Answer 2

A common cause of Excel VBA run-time error 1004 is an unqualified reference to an Excel object. When code uses Range, Selection, ActiveSheet, or similar objects without fully qualifying them, Excel can keep an internal reference that interferes with later runs. Microsoft documents this as a by-design behavior, especially when automation code is run more than once.

In the shown code, several references are unqualified or depend on the active selection:

Range("K1:L48").Select
Selection.NumberFormat = "0.000"
Range("K48").Activate
Selection.Copy
Worksheets(s1 - 1).Select
ActiveSheet.Paste

That pattern is fragile because Selection.Copy and ActiveSheet.Paste depend on whatever is currently selected and active at runtime.

A safer approach is to qualify every worksheet and range reference explicitly and avoid Select, Activate, Selection, and ActiveSheet where possible.

Example pattern:

Dim s1 As Long
s1 = Worksheets.Count

Worksheets(s1).Range("K1:L48").NumberFormat = "0.000"
Worksheets(s1).Range("K48").Copy Destination:=Worksheets(s1 - 1).Range("K48")

If the intent is to copy the whole range instead of only K48, use the full range as the source and destination:

Dim s1 As Long
s1 = Worksheets.Count

Worksheets(s1).Range("K1:L48").NumberFormat = "0.000"
Worksheets(s1).Range("K1:L48").Copy Destination:=Worksheets(s1 - 1).Range("K1")

Key fix:

Fully qualify Range with its worksheet.
Do not rely on Selection.
Do not rely on ActiveSheet.Paste.
Copy directly to the destination range.

If this started failing only recently, that still fits the documented behavior: code that leaves Excel object references ambiguous can work for a while and then fail when run again or when the active sheet/selection is not what the code expects.