VBA - Run Time Error 1004 'Application Defined or Object Defined Error'
I have an Excel document that copies a template sheet into a new sheet on the first time it runs. Any more sheets that follow this template are appended to the newly created sheet.
I'm getting the error in the title in this section of code:
If Worksheets("User Configuration").Cells(9, 15).Value = 1 Then
Worksheets("Cable Cards Template").Range("A1:J33").Copy
With Worksheets("Cable Cards")
**.Range(Cells(RangeStartRow, RangeStartColumn), Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlValues**
.Range(Cells(RangeStartRow, RangeStartColumn), Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlFormats
End With
Worksheets("Cable Cards Template").Shapes("Picture 1").Copy
Worksheets("Cable Cards").Paste Cells(RangeStartRow, RangeStartColumn)
Call Sheets.FormatCableCardRows
End If
Basically if the If
statement is true (the cell = 1), then a range on a particular sheet should be copied and pasted into the new sheet at the range given using PasteSpecial
for values and formatting. Following that, the "newly created" sheet should have an image copied into the top left cell of the template and then a subroutine is called to format the rows of the new sheet.
I'm getting the error at the first .Range
call after the With Worksheets("Cable Cards")
statement. I've tried not using the With
statement, copying values directly instead of paste-special etc. The weird thing is that this will run on the first go, when the new sheet is created via:
If (RangeStartRow = 1) Then
Worksheets.Add().Name = "Cable Cards" ' Create new sheet with given name only on first cable card
Columns(1).ColumnWidth = 9.43
Columns(6).ColumnWidth = 11
Columns(10).ColumnWidth = 9
Call FormatForA5Printing("Cable Cards", 71)
End If
but on the 2nd go, it fails entirely, with the Run Time Error 1004 'Application Defined or Object Defined Error'
. I'd appreciate any help.
Solution 1:
Your cells object is not fully qualified. You need to add a DOT
before the cells object. For example
With Worksheets("Cable Cards")
.Range(.Cells(RangeStartRow, RangeStartColumn), _
.Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlValues
Similarly, fully qualify all your Cells object.
Solution 2:
Solution #1: Your statement
.Range(Cells(RangeStartRow, RangeStartColumn), Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlValues
does not refer to a proper Range
to act upon. Instead,
.Range(.Cells(RangeStartRow, RangeStartColumn), .Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlValues
does (and similarly in some other cases).
Solution #2:
Activate Worksheets("Cable Cards")
prior to using its cells.
Explanation:
Cells(RangeStartRow, RangeStartColumn)
(e.g.) gives you a Range
, that would be ok, and that is why you often see Cells
used in this way. But since it is not applied to a specific object, it applies to the ActiveSheet
. Thus, your code attempts using .Range(rng1, rng2)
, where .Range
is a method of one Worksheet
object and rng1
and rng2
are in a different Worksheet
.
There are two checks that you can do to make this quite evident:
Activate your
Worksheets("Cable Cards")
prior to executing yourSub
and it will start working (now you have well-formed references toRange
s). For the code you posted, adding.Activate
right afterWith...
would indeed be a solution, although you might have a similar problem somewhere else in your code when referring to aRange
in anotherWorksheet
.-
With a sheet other than
Worksheets("Cable Cards")
active, set a breakpoint at the line throwing the error, start yourSub
, and when execution breaks, write at the immediate windowDebug.Print Cells(RangeStartRow, RangeStartColumn).Address(external:=True)
Debug.Print .Cells(RangeStartRow, RangeStartColumn).Address(external:=True)
and see the different outcomes.
Conclusion:
Using Cells
or Range
without a specified object (e.g., Worksheet
, or Range
) might be dangerous, especially when working with more than one Sheet
, unless one is quite sure about what Sheet
is active.