+ Reply to Thread
Results 1 to 2 of 2

Getting Run-time 1004 Delete Method of Range Class Failed with Two Macros active together

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Getting Run-time 1004 Delete Method of Range Class Failed with Two Macros active together

    Hi,

    I'm looking for some assistance with the following macros which are causing a conflict when active in the same workbook.

    I have a module which contains 4 macros similiar to MoveActiveRowtoCommenced(), which allow the user to move selected rows back and forth across 3 worksheets. These have been working fine until I recently added code to timestamp usernames and times for changed (with the code contained under the specific worksheet), and although the two don't directly relate to each other I'm now getting a 'Delete Method of Range Class Failed' when trying to move rows using any of the 4 contained in the module.

    Any pointers on why this is now happening would be much appreciated!

    Thanks

    David



    Sub Worksheet_Change(ByVal Target As Range)
        Dim x As Range
        Dim yourdate As String
        yourdate = Format(Now, "mmm-dd-yyyy hh:mm:ss am/pm") 'Format Today() as specified
        If Target.Column <= 21 Then
        Worksheets("Outstanding").Unprotect Password:="Password1"
        ''Cells(ActiveCell.Row, "U").Select
        Cells(ActiveCell.Row, "V").Value = Environ("USERNAME")
        Cells(ActiveCell.Row, "W").Value = yourdate
        Worksheets("Outstanding").Columns("V:W").AutoFit
        Worksheets("Outstanding").Protect Password:="Password1"
    End If
    
    End Sub


    Sub MoveActiveRowtoCommenced()
        Worksheets("Outstanding").Unprotect Password:="Password1"
        Worksheets("Commenced").Unprotect Password:="Password1"
         
        Application.ScreenUpdating = False
         
        Dim strSheetName, strCellAddress As String
        strSheetName = ActiveSheet.Name
        strCellAddress = ActiveCell.Address(False, False)
         
        Rows(ActiveCell.Row).Cut
        
        Sheets("Commenced").Select 'Change sheet name to whatever consolidated tab name is.
        'Range("A" & Range("A65536").End(xlUp).Row.(Offset(1, 0).Select
        Range("A65536").End(xlUp).Offset(1, 0).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
         
        Range("A" & ActiveCell.Row).Select
         
        Sheets(strSheetName).Select
        Range(strCellAddress).Select
         
        Rows(ActiveCell.Row).Delete
         
        Application.ScreenUpdating = True
        Worksheets("Commenced").Protect Password:="Password1"
        Worksheets("Outstanding").Protect Password:="Password1"
    End Sub

  2. #2
    Forum Contributor
    Join Date
    05-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Getting Run-time 1004 Delete Method of Range Class Failed with Two Macros active toge

    I should have added that the code fails at the line below
     Rows(ActiveCell.Row).Delete
    .

    Thanks

    David

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Run-time error '1004': Select method of Range class failed.
    By pspikym90 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-19-2012, 06:15 PM
  2. [SOLVED] Run-time Error '1004' Activate Method of Range class failed
    By gujugolf in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-13-2012, 12:08 PM
  3. [SOLVED] Run-time error '1004' ; Delete method of Range Class failed
    By Adam1987 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-14-2012, 06:43 AM
  4. [SOLVED] Delete method of range class failed - run time error 1004
    By plasma33 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-28-2012, 07:37 AM
  5. Select method of range class failed, Run time error 1004
    By smit127 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-01-2005, 08:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1