Run-time Error '1004' Method 'Range' Of Object '_Worksheet' Failed

Mar 25, 2009

I have an interesting error that only happens when there is one row of data in the worksheet (sheet2 or "Half Payout"). Rows 1 & 2 are headers, row 3 is when the data starts - if any. With either no rows of data or more than one the coding works just fine. Here is the exact error message I'm getting: Run-time error '1004': Method 'Range' of object '_Worksheet' failed.

The following code is supposed to sort the rows of data when opened and then activate the first open cell below B2.

View 6 Replies


Error 1004 Method 'Range' Of Object '_Worksheet' Failed

Jun 28, 2006

I have a cet of CommandButtons on sheet 1. The code for these buttons is in the code section of the sheet. A named range is referenced in this code. This named range is on sheet 2. Every time I try to reference this or any named range (from any other sheet), I get this "Run-time error '1004': Method 'Range' of object '_Worksheet' failed"

The last time I had this error I was able to fix it by moving the code to Module1. I tried that here, but it did not work (I can't figure out how to call it from the sheet). CommandButton. Here is the code from the sheet for one of the buttons:

Private Sub TBEnterUp_Click()
iLast = Range("WBDate_DayLast").Value '<<<<<<<
iItem = TBEnter.Value
If iItem = iLast Then
TBEnterUp.Visible = False
Exit Sub
End If
TBEnter.Value = iItem + 1
If iItem > 0 Then TBEnterDown.Visible = True
End Sub

It is a simple number advancer. It is working in the UserForm I took it from. I have included a sample file of the problem. Feel free to look at it and borrow anything in it that you might like.

View 2 Replies View Related

Method 'Range' Of Object '_Worksheet' Failed Error

May 20, 2008

I start in my "Action Plan for For Single Market" worksheet and if I click on the Command Button I then want to read in a couple of variables, and jump to a second worksheet (called "Market Action Plans") where I then want to copy a range, and then paste it in another area in that same sheet.

I thought I had worked out what to do, but when I execute I get a "run-time error 1004" when the macro gets to the "Range("Updated_Results").Copy" line. Excel describes the error as "Method 'Range' of object '_Worksheet' failed'" - which might as well be a foreign language as far as I am concerned!

Private Sub CommandButton2_Click()

Current_Market_Row = Range("Current_Market_Row")
First_Col_Action_Desc = Range("First_Col_Action_Desc")

Sheets("Market Action Plans").Select

Cells(Current_Market_Row, First_Col_Action_Desc).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Action Plan for Single Market").Select

End Sub
I am an occasional macro-creator and every now and again, I come across this type of problem where I can't get variables to be reconized, etc. I think it's because I don't understand enough about Private versus Public, and how to declare variables.

View 9 Replies View Related

Run Time Error 1004 - Method 'Range' Of 'Object'_Global' Failed

Feb 10, 2009

I am trying to build a macro which will format the columns of a spreadsheet - basically it inserts some columns, writes formulas and highlights them. Here is a code I have got so far...

When I try to run this I get a run time error 1004 - Method 'Range' of 'Object'_Global' failed. The part of the code
Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"

is highlighted in the debugger.

Can anyone tell me why this is happening, also it would be great if you could suggest better ways of writing this code - as I am new to vba programming and most of my macros are built using the recorder and then 'working' on them.

View 11 Replies View Related

Run-time Error '1004' :: Method 'Range' Of Object'_Global' Failed

Jan 13, 2010

I am trying to create a macro in my personal macro book such that whenever any workbook is opened the calculation settings (tools, options, calculation tab) are set to semiautomatic and do not calculate before save. The macro works when I am opening Excel itself (Book1) but when I open an already saved file it gives me

Run-time error '1004'

Method 'Range' of object'_Global' failed

From there I choose Debug, the VBE window comes up, and I hit F5 to continue the code without doing any actual debugging. Here is the code that I am using. This is in the Personal Macro book on the "This Workbook" section....

View 9 Replies View Related

"Run-time Error '1004', Method ' Range' Of Object '_Global' Failed"

Jul 26, 2006

I keep getting this "Run-time error '1004', Method ' Range' of object '_Global' failed" Here is the code that has the problem:

Option Explicit
Dim i As Long
Dim j As Long
Dim lDup As Long
Dim lRow As Long
Dim NoDupes As Collection
Dim rRng As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Swap1 As Variant
Dim Swap2 As Variant
Dim wks As Worksheet

Private Sub UserForm_Initialize()
Call DefaultSet
For lDup = 1 To 3
Call NonDuplicatesList(lDup)
Next lDup
End Sub.....................

It does not even loop once though the original UserForm_Initialize For/Next loop.

View 2 Replies View Related

Run-time Error '1004': Method 'Worksheets' Of Object '_Global' Failed

Jul 28, 2006

I am not familiar with Excel/VBA and I tried a couple of suggestions with no luck. The excel file contains three forms and three modules and it is intended to calculate and build Electrical panels. I didn't write the code. I am just trying to fix the error. I was trying to attach the .xls file but it is 178 KB. How can I post the whole code.

View 8 Replies View Related

Method 'Range' Of Object '_Worksheet' Failed

Jun 23, 2009

I can't seem to figure out why this keeps giving me that error...

I've checked the row and column values inside and they seem to be ok. blank_ee() is an array of strings.

View 7 Replies View Related

Error 1004 :: Method Range Of Object Global Failed

Mar 16, 2009

Method range of object global failed

When i run this ....

View 9 Replies View Related

RunTime Error 1004 Method Range Of Object Global Failed

Oct 7, 2011

Rows keep changing so I have variables to keep track of various locations.

At this point:

tot_new_place equals 30
tot_new_cnt equals 51

I want to take the data in B30:H30 and copy, auto fill to B51:H51

I am receiving the error on this line of code:

Range("B" & tot_new_place & "").Select
Selection.AutoFill Destination:=Range("R" & tot_new_place & "C2:R" & tot_new_cnt & "C9"), Type:=xlFillDefault

View 9 Replies View Related

Runtime Error 1004 Method Range Of Object Worksheet Failed

Feb 28, 2013

I M Getting Run Time Error 1004 Method Range Of Object _ Worksheet Failed

Option Explicit
Private Sub cboPart_AfterUpdate()
'On Error Resume Next


View 1 Replies View Related

Select A Range That Will Be Changing By Column: Method 'Range' Of Object '_worksheet' Failed

Jan 6, 2010

I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:

View 2 Replies View Related

Run-time Error '1004' Method 'Add' Of Object ' Sheets' Failed Adding Multiple Sheets

Aug 9, 2007

I have been running a simulation for about 18 hours now and just received:

Run-time error '1004':
Method 'Add' of object ' Sheets' failed

I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:

ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)

The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...

View 9 Replies View Related

Run-time Error '1004' ; AutoFilter Method Of Range Class Failed

Oct 20, 2008

I lost 2 days trying to discover the bug in my program, but I coudn't find the right answer.

How can I activate the AutoFilter to show me the rows selected with "YES" ?

This is the code... The compiler stops always at the command Selection.AutoFilter.

View 14 Replies View Related

Run-time Error '1004' - PasteSpecial Method Of Range Class Failed ....

Oct 16, 2009

This very simple macro in Personal.xlsb is driving me crazy!

I want to paste a previously selected and copied range at the current position as values. The range will seldom be the same as previously, and the position where it will be posted will be random too.

The code is: ...

View 13 Replies View Related

Run Time Error '1004' Autofill Method Of Range Class Failed

Oct 26, 2009

I found the following code on your forum, but get the following error:

Run time error '1004'. Autofill method of Range class failed.

The error occurs when the spreadsheet either have 1 row of data completed or no data, can I get code to ignore the autofill when I only have 1 row or no rows completed in various spreadsheets. I have attached a copy of the spreadsheet.

View 2 Replies View Related

Run Time Error '1004': PasteSpecial Method Of Range Class Failed

Jul 1, 2008

This file has been running just fine....

But now I got this

Run Time error '1004':
PasteSpecial method of Range Class failed.

View 9 Replies View Related

Run-time Error '1004' : Select Method Of Range Class Failed

Jul 28, 2006

I have a userform that allows the user to view, modify or delete individual records (rows) of a worksheet in a workbook. If a particular item in a record is modified, a check occurs to ensure that the values for that item remain unique. All of that works exactly the way I need. The breakdown occurs when the records are then autosorted by a the values in a particular column. After the autosort, another column of values is copied and pasted to a different worksheet within the same workbook. For this to work properly, I must have the userform open along with the worksheet containing the records. If the userform is open with any other worksheet open, I get the following VBA error message "Run-time Error '1004' : Slect method of Range class failed". I need to be able to open the userform with any worksheet active and not experience this error when the autosort, copy/paste occurs. This is the code for the user form, the module for autosort, copy/paste and checking for unue values.

This is the sub in the worksheet with the records

'This checks for unique values
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim LLoop As Integer
Dim LTestLoop As Integer
Dim Lrows As Integer
Dim LRange As String
Dim LChangedValue As String
Dim LTestValue As String
Dim smessage As String
'Test first 7 rows in spreadsheet for uniqueness
Lrows = 8
LLoop = 2
'Check first 7 rows in spreadsheet
While LLoop <= Lrows.........................

View 3 Replies View Related

Run-time Error '1004': Select Method Of Range Class Failed

Apr 7, 2007

I get the following error: Run-time error '1004': Select method of Range class failed at the following code line:


View 4 Replies View Related

Run-time Error '1004' Pastespecial Method Of Range Class Failed

May 22, 2007

I am using the following code and I'm getting a Run-time error '1004' error. When I reconstruct the macro one line at a time and run the macro between adding each new line - no error. After reconstructing the macro in its entirety, I can run it once with no error. However, if I try to run it again immediately after that, I get the error and I keep getting the error every time I run it from there on. I dont understand how it can work once and then stop working. Here is the full

Sub MoveToRoster()
ActiveSheet. Unprotect
Dim item As Long
Dim myString1 As String
Dim myString2 As String
Dim myString3 As String
item = InputBox("Please Confirm The Row Number Of The Child To Be Moved To The Roster.")
myString1 = "c" & item & ":e" & item
myString2 = "g" & item & ":n" & item
myString3 = "c" & item & ":e" & item & ",g" & item & ":p" & item ..........................

View 2 Replies View Related

"Run-time Error '1004' (Select Method Of Range Class Failed)

Aug 19, 2009

Newcomer to Excel 2007 but long time programmer for Excel 2003. I am having problems with code that I feel should work. Code fails at the Select command with a "Run-time error '1004': Select method of Range class failed:

View 3 Replies View Related

Run-time Error '1004': Methd 'Range' Of Object '_Global' Failed

Aug 5, 2006

I have constructed the following code to set the print area of worksheets that have been selected to print to the range referenced in a worksheet level named range "xPrintArea". This named range is set using the OFFSET function. The procedure also sets the left footer to be a copyright notice that is also contained in a cell referenced by a named range.

Set oPrintArea = Range(sPrintAreaName)

is generating the error "Methd 'Range' of object '_Global' failed".

Note that the line

Set oCopyrightNotice = Range("CopyrightNotice")

does not generate this error. From what I've been able to determine from other research on this forum and others, I believe the problem is that I need to more fully qualify the object which Range(sPrintAreaName) is referencing. I've already tried to use

Set oPrintArea = wkSht.Range(sPrintAreaName).................

View 4 Replies View Related

Method 'copy' Of Object '_worksheet' Failed

Feb 15, 2007

From the current open book, I'm opening a 2nd book, then copying all worksheets from the 2nd book that meet criteria, into 1st book, (in the same order), but am getting Method 'Copy' of object '_Worksheet' failed error. What am I doing wrong?
How to OVERWRITE worksheets?We'll be running the same process with new data, so also need to overwrite worksheets in 1st book.

Sub Build_Branch_File()
Dim FileName As String
Dim Wkb As Workbook
Dim Ws As Worksheet
Dim WNum As String
Dim Tnum As String
Dim RegionNo As Integer
Dim Original_Wb As Workbook

With Application
. ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set Original_Wb = ThisWorkbook .........................

View 9 Replies View Related

Error 'Method Range Of Object Global Failed' On FindNext Method

Dec 10, 2008

I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.

The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.

View 3 Replies View Related

VBA- Getting A Run Time Error 1004 Delete Method Of Worksheet Class Failed

Dec 28, 2009

i am getting a run time error 1004 Delete method of worksheet class failed,


Sub Save()
Dim myName As String, myFolder As String, e
Dim fso As Object, temp As String


Set fso = CreateObject("Scripting.FileSystemObject")

myFolder = "C:UsersRecsDocumentsTestVBA & ExcelEmail_Files_Temp" & Year(Date) & "" & Format$(Date, "mmm")
For Each e In Split(myFolder, "")
temp = temp & IIf(temp = "", "", "") & e
If fso.FolderExists(temp) = False Then fso.CreateFolder (temp)

View 9 Replies View Related

Method 'Add' Of Object 'CommandBarControls' Failed (run-time Error '-2147467259')

Sep 6, 2006

i get this error on the line in bold Method Add' of object 'CommandBarControls' failed (run-time error '-2147467259'). im not sure why its giving that problem, but im not very fluent in custom toolbars.

'//The following two procedures add a custom menu to the workbook programmatically//'
'//and then delete it//'
Public currentMonth As String
Sub CreateMenu()
Dim mybar As CommandBar
Dim myControl As CommandBarControl
Set mybar = Application.CommandBars.Add( Name:="CustomButtons", _
Position:=msoBarBottom, Temporary:=True)
mybar.Visible = True
Set myControl = mybar.Controls _
.Add(Type:=msoControlButton, ID:=1)
With myControl...........................

View 3 Replies View Related

Error 1004 - Range Of Object Failed?

Jan 30, 2013

It is designed to submit data specific cells from a userform. It works fine when I select and day, 1,2,3, etc. until I get to day 27-31. For some reason I get a Range of Object Failed error 1004 every time. I don't understand what's changing to cause the issue. The red text near the bottom is the one that it tells me is the problem.

Private Sub Submit_Click()
Dim ws As Worksheet
Set ws = activesheet


View 9 Replies View Related

Run Time Error 50034 Method Export Of Object Vb Component Failed

Jan 30, 2010

I keep getting this error on this line

[code]ThisWorkbook.VBProject.VBComponents("Module2").Export FileName[CODE]

of this

View 3 Replies View Related

Delete Method Of Range Class Failed - Error 1004

Feb 9, 2012

I'm attempting to write an OutLook 2007 macro that extracts excel spreadsheets from incoming email and saves them as a .csv file. Before saving the file I need to delete the header row. I am getting a "Error 1004 - Delete method of range class failed" error when running this code.

Here is the code: (the bolded line is where I'm getting the error)

Public Sub ConvertToCsv2()
Dim xls As Excel.Application
Dim oWB As Excel.Workbook
Dim tmp As String
Dim ws As Excel.Worksheet


View 7 Replies View Related

Sort Method Of Range Class Failed Error 1004

Apr 6, 2012

My code is :
Public Const GRAPH_PutData_COL1 = "A"
Public Const GRAPH_PutData_COL2 = "B"

Range(GRAPH_PutData_COL1 & "1").Sort Key1:=Range(GRAPH_PutData_COL2 & "1"), _
Order1:=xlAscending, _

it is showing error, "sort method of range class failed error 1004"

Above error only showing when first record is empty...

View 2 Replies View Related

Copyrights 2005-15, All rights reserved