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

User · Answer

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 your Sub and it will start working  now you have well-formed references to Ranges   For the code you posted  adding  Activate right after With    would indeed be a solution  although you might have a similar problem somewhere else in your code when referring to a Range in another Worksheet  With a sheet other than Worksheets  Cable Cards   active  set a breakpoint at the line throwing the error  start your Sub  and when execution breaks  write at the immediate window  Debug 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

User · Answer

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

User · Answer

Assgining a value that starts with a     will kick in formula evaluation and gave in my case the above mentioned error  1004  Prepending it with a space was the ticket for me

[vba] VBA - Run Time Error 1004 'Application Defined or Object Defined Error'

Examples related to vba

Examples related to excel

Examples related to excel-2003