1

I have this code below

Dim Lasteuro As Long ThisWorkbook.Sheets("Euro Cash").Range("A2:AF").Sort Key1:=Range("T2:T"), Order1:=xlAscending, Key2:=Range("V:V"), Order2:=xlAscending, Header:=xlYes Lasteuro = Sheets("Euro Cash").Range("a1").End(xlDown).Row Sheets("Euro Cash").Range("A:U").AutoFilter Field:=17, Criteria1:=Array("LNCCP", "LNLCHSCM"), Operator:=xlFilterValues 'Sec No.' Sheets("Euro Cash").Range("D2:D" & Lasteuro).Copy Sheets("Master").Range("K6").PasteSpecial Paste:=xlPasteValues 'Cpty Name' Sheets("Euro Cash").Range("F2:F" & Lasteuro).Copy Sheets("Master").Range("L6").PasteSpecial Paste:=xlPasteValues 'Break' Sheets("Euro Cash").Range("I2:I" & Lasteuro).Copy Sheets("Master").Range("M6").PasteSpecial Paste:=xlPasteValues 'Age' Sheets("Euro Cash").Range("P2:P" & Lasteuro).Copy Sheets("Master").Range("P6").PasteSpecial Paste:=xlPasteValues Sheets("Euro Cash").ShowAllData

However upon running, I get:

Run-time error '1004' : Method Range of object _Global failed.

I clicked on debug, it highlights this

ThisWorkbook.Sheets("Euro Cash").Range("A2:AF").Sort Key1:=Range("T2:T"), Order1:=xlAscending, Key2:=Range("V:V"), Order2:=xlAscending, Header:=xlYes

So what I did is change Thisworkbook.Sheets to Sheets - still same error.. what should I do?

Improve this question

1 Answer 1

Reset to default
2

The use of the Range object within the Range .Sort method is open to interpretation as to what the parent worksheet is.

Dim Lasteuro As Long With Sheets("Euro Cash") If .AutoFilterMode Then .AutoFilterMode = False Lasteuro = .Cells(Rows.Count, 1).End(xlUp).Row .Range("A:AF").Sort Key1:=.Columns(20), Order1:=xlAscending, _ Key2:=.Columns(22), Order2:=xlAscending, _ Header:=xlYes .Range("A:U").AutoFilter Field:=17, Criteria1:=Array("LNCCP", "LNLCHSCM"), _ Operator:=xlFilterValues 'Sec No.' .Range("D2:D" & Lasteuro).Copy Sheets("Master").Range("K6").PasteSpecial Paste:=xlPasteValues 'Cpty Name' .Range("F2:F" & Lasteuro).Copy Sheets("Master").Range("L6").PasteSpecial Paste:=xlPasteValues 'Break' .Range("I2:I" & Lasteuro).Copy Sheets("Master").Range("M6").PasteSpecial Paste:=xlPasteValues 'Age' .Range("P2:P" & Lasteuro).Copy Sheets("Master").Range("P6").PasteSpecial Paste:=xlPasteValues If .AutoFilterMode Then .AutoFilterMode = False End With

Note the use of .Range and not Range. This means that the parent is the one defined in the With ... End With statement. You are only required to indicate the first cell when defining the key.

After looking closer at all of your code (and reviewing the comments provided) I've changed the sort range so that it recognizes the column header labels in row 1.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

do i use T2 and V3? not "T:T" and "V:V"?
You can declare each key as the top cell or the whole column. It is just personally preference. btw, it should be T2 and V2, not T2 and V3.
im now getting application-defined or object defined error
Dim Lasteuro As Long With ThisWorkbook.Sheets("Euro Cash") .Range("A2:AF").Sort Key1:=.Range(T), Order1:=xlAscending, Key2:=.Range(V), Order2:=xlAscending, Header:=xlYes End With Lasteuro = Sheets("Euro Cash").Range("a1").End(xlDown).Row Sheets("Euro Cash").Range("A:U").AutoFilter Field:=17, Criteria1:=Array("LNCCP", "LNLCHSCM"), Operator:=xlFilterValues
It concerns me that you are using a range that starts in row 2 and stating that you have a header but later using full column references for the autofilter. Ar your column header labels in row 1 or row 2?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.