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
Bookmarks