Run-time Error 1004, Application defined or object-defined error

Question

I've searched everywhere to see why I'm getting this error. Basically once I get to the last line the "Selection.AutoFill Destination:=Range("G2:M" & LR)" I get the error. The code works if in a separate sub, by itself. Therefore I'm assuming the code above it is somehow affecting it?

Sub Certainsheets()

Dim Wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
Dim LR As Long
Dim rTable As Range
Dim strCellREF2Txt As String
Dim strFILEname As String
Dim WS As Worksheet


'copy from ThisWorkbook
'Set wb2 = Workbooks(2)
 Set wb2 = Workbooks.Open("C:\Users\asharma\Desktop\Loan Application\Loan 
 Data.xls")

'To this
Set Wb1 = ThisWorkbook

'Copying data from Loan Data file
Set tbl = wb2.Sheets(1).Range("A1").CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Copy
'wb2.Sheets(1).Range("A1").CurrentRegion.Copy

'Pasting data into AOL DATA Tab
Wb1.Activate
Sheets("AOL DATA").Range("A10000").End(xlUp).Offset(1, 0).PasteSpecial 
xlValues

'Wb1.Sheets(1).Range("A1").Select.PasteSpecial Paste:=xlPasteValues, 
'Operation:=xlNone, SkipBlanks _
    ':=False, Transpose:=False

Application.CutCopyMode = False
wb2.Close

'REMOVING DUPLICATES
'Sheets("AOL DATA").Range("$A:$E").RemoveDuplicates Columns:=1, Header:=xlNo

'This part Autofills the formulas till the last row. 

LR = Range("A" & Rows.Count).End(xlUp).Row
Sheets("AOL DATA").Range("G2:M2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G2:M" & LR)

End sub'

Any help would be appreciated

Sorry I don't understand (new to vba). I've declared LR and then used it to find the last row in column A. I haven't set it to a particular cell. — Sky
Put Debug.Print LR right before the problematic line and the results will be printed in the immediate window (open it from the View menu). — jivko
I added it right above the last line which is causing the error and re-ran the code. Received a "1" in the immediate pane. Don't really know what than means.. — Sky
Try LR = Sheets("AOL DATA").Range("A" & Rows.Count).End(xlUp).Row and then check the value of LR — Tim Williams

SierraOscar SierraOscar · Accepted Answer · 2017-05-31T07:23:22

You need to qualify your ranges with the actual sheet, otherwise VBA will default to the ActiveSheet object which may not be what you're expecting in your code.

You can re-write your code as follows:

Sub Certainsheets()

Dim loanWorkbook As Excel.Workbook
Dim aolSheet     As Excel.Worksheet
Dim dataTable    As Excel.Range

Set loanWorkbook = Workbooks.Open("C:\Users\asharma\Desktop\Loan Application\Loan Data.xls")

Set aolSheet = ThisWorkbook.Sheets("AOL DATA")

Set dataTable = loanWorkbook.Sheets(1).Range("A1").CurrentRegion

With dataTable.Offset(1, 0)
    aolData.Range("A" & aolData.Rows.Count).End(xlUp).Offset(1, 0).Value = _
    .Resize(.Rows.Count - 1, .Columns.Count).Value
End With

loanWorkbook.Close

With aolSheet
    .Range("G2:M2").AutoFill .Range("G2:M" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With

End Sub

The AutoFill() method requires the source range to be included as part of the destination range. I suspect because of your code's reliance on ActiveSheet object that you're unknowingly specifying two ranges on different sheets, hence the code fails.

Run-time Error 1004, Application defined or object-defined error

1 Answers