+ Reply to Thread
Results 1 to 5 of 5

Copy, Paste Macro Error 1004

Hybrid View

  1. #1
    Registered User
    Join Date
    01-11-2011
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    38

    Copy, Paste Macro Error 1004

    I have a worksheet (ABC) that pulls data from another sheet that has unique data entries (DEF) beginning every 9 lines and has an undefined length. For ABC I have formulas that reference cells for each entry, so I created a loop to copy the 2nd row of the sheet which has all of the formulas and paste it 9 rows down, then delete the 8 rows in between that I do not need.
    I am having an issue with my code that gives me the error "Run-time error '1004': Method 'Range' of object'_Worksheet' failed". I defined variables for the sheets and had the errors so I tried using the full "Sheets("ABC").Range" code which didn't fix it, so I tried selecting the sheet then pasting to the selection which also did not work. I had a similar issue on another of my macros but was able to figure it out and fix the problem with these troubleshooting tactics.
    Below is my code:
    Sub SheetExtend()
        Dim ABC As Worksheet
        Dim DEF As Worksheet
        Dim NR As Long 'Next empty row in ABC
        Dim LR As Long
        
        Set ABC = Sheets("ABC")
        Set DEF = Sheets("DEF")
        
        Application.ScreenUpdating = False
        
        LR = DEF.Range("A" & Rows.Count).End(xlUp).Row      'Last row with data in DEF worksheet
        NR = ABC.Range("A" & Rows.Count).End(xlUp).Row + 1
        ABC.Range(Cells(NR - 1, 1), Cells(NR - 1, 14)).Copy
        ABC.Range(Cells(NR, 1)).Paste
            For NR = ABC.Range("A" & Rows.Count).End(xlUp).Row + 1 To LR Step 1
                Sheets("ABC").Range(Cells(NR - 1, 1), Cells(NR - 1, 14)).Copy
                ABC.Range(Cells(NR + 8, 1), Cells(NR + 8, 14)).Paste
                Cells(NR, 1).Rows("1:8").EntireRow.Delete Shift:=xlUp
            Next NR
        Application.ScreenUpdating = True
    End Sub
    Thanks in advance

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Copy, Paste Macro Error 1004

    Which row is causing the error?

    Can you upload an example workbook?
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    01-11-2011
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Copy, Paste Macro Error 1004

    The error comes in both of the lines where the data is pasted (ABC.Paste).
    Example.xlsm

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Copy, Paste Macro Error 1004

    Syntax is correct now:

    Sub SheetExtend()
        Dim ABC As Worksheet
        Dim DEF As Worksheet
        Dim NR As Long 'Next empty row in ABC
        Dim LR As Long
        
        Set ABC = Sheets("ABC")
        Set DEF = Sheets("DEF")
        
        Application.ScreenUpdating = False
        
        LR = DEF.Range("A" & Rows.Count).End(xlUp).Row      'Last row with data in DEF worksheet
        NR = ABC.Range("A" & Rows.Count).End(xlUp).Row + 1
        ABC.Range(Cells(NR - 1, 1), Cells(NR - 1, 14)).Copy ABC.Cells(NR, 1)
            For NR = ABC.Range("A" & Rows.Count).End(xlUp).Row + 1 To LR Step 1
                Sheets("ABC").Range(Cells(NR - 1, 1), Cells(NR - 1, 14)).Copy ABC.Range(Cells(NR + 8, 1), Cells(NR + 8, 14))
                Cells(NR, 1).Rows("1:8").EntireRow.Delete Shift:=xlUp
            Next NR
        Application.ScreenUpdating = True
    End Sub
    Check the operation is correct and does what you want.

  5. #5
    Registered User
    Join Date
    01-11-2011
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Copy, Paste Macro Error 1004

    That's perfect.
    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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