Run-time error 1004 in VBA-Excel when Copying Rows

Question

I have just learnt some VBA in order to make some time consuming reports. The VBA runs flawlessly only if my raw data is below 100000 rows. If it has more than 100000 rows, its throwing me an error:

RUN-time error '1004' - Method 'Range' of object'_Global' failed

Code I have written:

Sub OSP_UntouchedReport()

Dim Magisk As Long
With ActiveSheet
Magisk = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Sheets("RAW").Select
Range("BI1").Value = ("LDAP MAP")
Range("BI2").FormulaR1C1 = "=VLOOKUP(RC[-2],Reference!C[-52]:C[-51],2,0)"
Range("BI2").AutoFill Destination:=Range("BI2:BI" & Magisk)
Range("BI2:BI" & Magisk).Copy
Range("BI2:BI" & Magisk).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

Range("BI1").AutoFilter Field:=61, Criteria1:="#N/A"
Range("A1:BI1" & Magisk).Copy
Sheets.Add.Name = "NA"
Sheets("NA").Paste
    Application.CutCopyMode = False

Sheets("RAW").Select
Range("BI1").AutoFilter Field:=61, Criteria1:="<>#N/A"
Range("A1:BI1" & Magisk).Copy
Sheets.Add.Name = "MAPPED"
Sheets("MAPPED").Paste
    Application.CutCopyMode = False

Sheets("RAW").Select
Cells.AutoFilter

Thanks!

+100K VLookups, Yikes!!! Using a Scripting.Dictionary and Arrays would speed up your code tremendously. — TinMan
Nice catch Vityata. I was caught up with the fact that Range("BI2").FormulaR1C1 = "=VLOOKUP(RC[-2],Reference!C[-52]:C[-51],2,0)" actually works. It evaluates to =VLOOKUP(XFD1,Reference!XDF:XDG,2,0). Column XFD1 is Column number 16384. Geez, what is really going on here. — TinMan

Vityata Vityata · Accepted Answer · 2018-07-02T09:18:57

This line here:

Range("A1:BI1" & Magisk).Copy

actually produces something like:

Range("A1:BI1100").Copy

when the value of Magisk is 100. And you are expecting:

Range("A1:BI100").Copy

Thus, change the line to:

Range("A1:BI" & Magisk).Copy

And you will be ok. Read this, whenever you have time - How to avoid using Select in Excel VBA

Run-time error 1004 in VBA-Excel when Copying Rows

1 Answers