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

Question

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.

Answer 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.