VBA Runtime Error 1004 "Application-defined or Object-defined error" when Selecting Range

Question

I am having an issue with a Error 1004 "Application-defined or Object-defined error" when selecting a range.

I am still able to select rows (ie Rows("21:21").select) and to select ranges in other sheets of the same workbook. I do not believe the error is in the code. Maybe its some setting I am unaware of?

I have used the exact same code many times before but for some reason I cannot make it function in this sub (I have commented where the error occurs)...

Sub CopySheet1_to_PasteSheet2()

    Dim CLastFundRow As Integer
    Dim CFirstBlankRow As Integer

    'Finds last row of content
    Windows("Excel.xlsm").Activate
    Sheets("Sheet1").Activate
    Range("C21").Select
         '>>>Error 1004 "Application-defined or Object-defined error" Occurs
    Selection.End(xlDown).Select
    CLastFundRow = ActiveCell.Row
    'Finds first row without content
    CFirstBlankRow = CLastFundRow + 1

    'Copy Data
    Range("A21:C" & CLastFundRow).Select
    Selection.Copy
    'Paste Data Values
    Sheets("PalTrakExport PortfolioAIdName").Select
    Range("A21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    'Bring back to top of sheet for consistancy
    Range("A21").Select
    Range("A1").Select
End Sub

I need to get all fancy in my copying as the amount of rows will change frequently. Again, the below code has been used before without error... but not in this instance.

Dim CLastFundRow As Integer
Dim CFirstBlankRow As Integer

'Finds last row of content
Windows("Excel.xlsm").Activate
Sheets("Sheet1").Activate
Range("C21").Select
     '>>>Error 1004 "Application-defined or Object-defined error" Occurs
Selection.End(xlDown).Select
CLastFundRow = ActiveCell.Row
'Finds first row without content
CFirstBlankRow = CLastFundRow + 1

Answer 1

Perhaps your code is behind Sheet1, so when you change the focus to Sheet2 the objects cannot be found? If that's the case, simply specifying your target worksheet might help:

Sheets("Sheet1").Range("C21").Select

I'm not very familiar with how Select works because I try to avoid it as much as possible :-). You can define and manipulate ranges without selecting them. Also it's a good idea to be explicit about everything you reference. That way, you don't lose track if you go from one sheet or workbook to another. Try this:

Option Explicit

Sub CopySheet1_to_PasteSheet2()

    Dim CLastFundRow As Integer
    Dim CFirstBlankRow As Integer
    Dim wksSource As Worksheet, wksDest As Worksheet
    Dim rngStart As Range, rngSource As Range, rngDest As Range

    Set wksSource = ActiveWorkbook.Sheets("Sheet1")
    Set wksDest = ActiveWorkbook.Sheets("Sheet2")

    'Finds last row of content
    CLastFundRow = wksSource.Range("C21").End(xlDown).Row
    'Finds first row without content
    CFirstBlankRow = CLastFundRow + 1

    'Copy Data
    Set rngSource = wksSource.Range("A2:C" & CLastFundRow)

    'Paste Data Values
    Set rngDest = wksDest.Range("A21")
    rngSource.Copy
    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'Bring back to top of sheet for consistancy
    wksDest.Range("A1").Select

End Sub

Answer 2

It's a bit late but might be helpful for future reference. I had just had the same issue and I think it's because the macro was placed at the worksheet level. Right click on the modules node on the VBA project window, click on "Insert" => "Module", then paste your macro in the new module (make sure you delete the one recorded at the worksheet level).

Answer 3

I as well had the same problem and nearly drove crazy. The solution was pretty unexpected.

My Excel is shipped out by default that I enter formulas in an Excel-Cell as followed:

=COUNTIF(Range; Searchvalue)
=COUNTIF(A1:A10; 7) 'Example

Please note, that parameters are separated with a semicolon;. Now if you paste exactly that string into a formula within VBA, for example like:

Range("C7").FormulaArray = "=COUNTIF(A1:A10; 7)" 'this will not work

You will get this 1004-error with absolutely no explanation. I spent hours to debug this.. All you have to do is replace all semicolon with commas.

Range("C7").FormulaArray = "=COUNTIF(A1:A10, 7)" 'this works

Answer 4

The same thing happened to me. In my case most of the worksheet was in protected mode (though the cells relevant to the macro were unlocked). When I disabled the protection on the worksheet, the macro worked fine...it seems VBA doesn't like locked cells even if they are not used by the macro.

Answer 5

Some operations in Excel are limited by available Memory. If you repeat the same process over and over it could produce a memory overflow and excel will not be able to repeat it anymore. This happened to me while trying to create several sheets in the same workbook.

Answer 6

You may receive a "Run-time error 1004" error message when you programmatically set a large array string to a range in Excel 2003

In Office Excel 2003, when you programmatically set a range value with an array containing a large string, you may receive an error message similar to the following:

Run-time error '1004'. Application-defined or operation-defined error.

This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.

To work around this issue, edit the script so that no cells in the array contain a character string that holds more than 911 characters.

For example, the following line of code from the example code block below defines a character string that contains 912 characters:

Sub XLTest()
Dim aValues(4)

  aValues(0) = "Test1"
  aValues(1) = "Test2"
  aValues(2) = "Test3"

  MsgBox "First the Good range set."
  aValues(3) = String(911, 65)

  Range("A1:D1").Value = aValues

  MsgBox "Now the bad range set."
  aValues(3) = String(912, 66)
  Range("A2:D2").Value = aValues

End Sub

Other versions of Excel or free alternatives like Calc should work as well.

Answer 7

I could remove the error (Run-time error '1004'. Application-defined or operation-defined error) by defining the counters as Single

Answer 8

You need to find out the actual reason for this common error code: 1004. Edit your function/VBA code and run your program in debug mode to identify the line which is causing it. And then, add below piece of code to see the error,

On Error Resume Next
//Your Line here which causes 1004 error
If Err.Number > 0 Then
  Debug.Print Err.Number & ":" & Err.Description
End If

Note: Debug shortcut keys i use in PC: Step Into (F8), Step Over (Shift + F8), Step Out (Ctrl + Shift + F8)

Answer 9

I had a similar problem & fixed it applying these steps:

1. Unprotecting the sheet that I want to edit
2. Changing the range that I had selected by every single cell in the range (exploded)

I hope this will help someone.

Answer 10

You have to go to the sheet of db to get the first blank row, you could try this method.

Sub DesdeColombia ()    
  Dim LastRowFull As Long

  'Here we will define the first blank row in the column number 1 of sheet number 1:
  LastRowFull = Sheet1.Cells(Rows.Count,1).End(xlUp).Offset(1,0).Row

  'Now we are going to insert information
  Sheet1.Cells(LastRowFull, 1).Value = "We got it"    
End Sub

Answer 11

You can use the following code (For example if one was to want to copy cell data from Sheet2 to Sheet1).

Sub Copy
Worksheets("Sheet1").Activate                    
Worksheets("Sheet1").Range(Cells(i, 6), Cells(i, FullPathLastColumn)).Copy_
Destination:=Worksheets("Sheet2").Cells(Path2Row, Path2EndColumn + 1)
End Sub

Answer 12

I had a similar issue, but it turns out I was just referencing a cell which was off the page {i.e. cells(i,1).cut cells (i-1,2)}

Answer 13

I had this issue during VBA development/debugging suddenly because some (unknown to me) function(ality) caused the cells to be locked (maybe renaming of named references at some problematic stage).

Unlocking the cells manually worked fine:

Selecting all worksheet cells (CTRL+A) and unlock by right click -> cell formatting -> protection -> [ ] lock (may be different - translated from German: Zellen formatieren -> Schutz -> [ ] Gesperrt)