VBA - Run Time Error 1004 'Application Defined or Object Defined Error'

excel, excel-2003, vba

Solution

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.

Problem

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.

Original source