MS Excel vba Run-Time Error '1004'

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:

1. Fully qualify Range with its worksheet.
2. Do not rely on Selection.
3. Do not rely on ActiveSheet.Paste.
4. 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.

• Excel automation fails second time code runs
• How to fix a macro run time error 1004 - Microsoft Q&A