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            gt  gt  gt 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   amp  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        gt  gt  gt Error 1004  Application-defined or Object-defined error  Occurs Selection End xlDown  Select CLastFundRow   ActiveCell Row  Finds first row without content CFirstBlankRow   CLastFundRow   1

User · Answer

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

User · Answer

I had a similar problem  amp  fixed it applying these steps    Unprotecting the sheet that I want to edit Changing the range that I had selected by every single cell in the range  exploded    I hope this will help someone

User · Answer

I had a similar issue when trying to loop on each sheet of a workbook  To resolve it I did something like this  dim mySheet as sheet  for each mysheet in myWorkbook sheets      mySheet activate     activesheet range  A1   select      with Selection      any wanted operations here     end with  next   And it worked just fine  I hope you can adapt it in your situation

User · Answer

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

User · Answer

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 - gt  cell formatting - gt  protection - gt      lock  may be different - translated from German  Zellen formatieren - gt  Schutz - gt      Gesperrt

User · Answer

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

User · Answer

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   amp  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

User · Answer

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

User · Answer

I am also having the same problem and I solved by as below  in macro have a variable called rownumber and initially i set it as zero  this is the error because no excel sheet contains row number as zero  when i set as 1 and increment what i want   now its working fine

User · Answer

I ran into the same issue and found that the individual that created the worksheet had several columns locked   I removed the protection and everything worked as designed

User · Answer

I also had a similar issue  After copying and pasting to a sheet I wanted the cursor  selected cell to be A1 not the range that I just pasted into   Dim wkSheet as Worksheet Set wkSheet   Worksheets  lt sheetname gt    wkSheet  A1   Select   but got a 400 error which was actually a 1004 error   You need to activate the sheet before changing the selected cell this worked  Dim wkSheet as Worksheet Set wkSheet   Worksheets  lt sheetname gt    wkSheet Activate wkSheet  A1   Select

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  0 Then   Debug Print Err Number  amp       amp  Err Description End If   Note  Debug shortcut keys i use in PC  Step Into  F8   Step Over  Shift   F8   Step Out  Ctrl   Shift   F8

User · Answer

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

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

Examples related to vba

Examples related to excel