CODEWITHSUNDEEP
excelvbamacros
Luke Turnbull
Luke Turnbull

Reputation: 55

Application Defined or Object Defined Error

I am getting this error on the following line of code and cant understand why. Please could someone explain why this is happening? 

Sub Resource_Details()

    Dim x As Workbook
    Dim y As Workbook

    Set x = ThisWorkbook
    Set y = Workbooks.Open("C:\Users\turnbull\Documents\Global Unmet Demand\3-extract-Resource details.xls")
    y.Sheets("Sheet1").Range("I:O, AB:AJ").EntireColumn.Delete
    y.Sheets("Sheet1").Range("OFFSET(Sheet1!A2,0,0,COUNT(Sheet1!$A:$A),44)").Copy
    x.Sheets("Resource Details").Range("A2").PasteSpecial xlValues

    End Sub

The error is on this line: 

y.Sheets("Sheet1").Range("OFFSET(Sheet1!A2,0,0,COUNT(Sheet1!$A:$A),44)").Copy

I have used this line of code on the following Macro and it works correctly. I am confused as to why it works on one macro and not the other. 

Sub Unmet_Details()

Dim x As Workbook
Dim y As Workbook

Set x = ThisWorkbook
Set y = Workbooks.Open("C:\Users\turnbull\Documents\Global Unmet Demand\2-extract-Unmet details.xls")
x.Activate
Sheets("Unmet Details").Activate
Range("Table2").Select
Selection.Delete
y.Activate
AutoFilterMode = False
Range("A1:AR1").AutoFilter
Range("A1:AR1").AutoFilter Field:=8, Criteria1:=Array("Fulfilled", "Requested", "Partially Assigned", "Soft Booked", "Assigned"), Operator:=xlFilterValues
y.Sheets("Sheet1").Range("OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A),44)").Copy
x.Sheets("Unmet Details").Range("A2").PasteSpecial xlValues

Upvotes: 0

Views: 64

Answers (1)

CallumDA
CallumDA

Reputation: 12113

This is one way to go about it (note this is untested)

Replace:

y.Sheets("Sheet1").Range("OFFSET(Sheet1!A2,0,0,COUNT(Sheet1!$A:$A),44)").Copy

With

y.Worksheets("Sheet1").Range("A1").Resize(WorksheetFunction.COUNTA(y.Worksheets("Sheet1").Range("A:A")),44).Offset(0,0)

And use the offset function at the end to change the range in each loop if that is what you require (I gather from your comment...). You would need a loop though, which your code above doesn't currently have.

Upvotes: 1

Related Questions