Sorting using VBA

Question

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?

score 2 · Accepted Answer · 2015-07-18 06:40:11Z

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.

edited Jul 18, 2015 at 6:40

answered Jul 18, 2015 at 6:07

user4039065

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

9 Comments

pucktack Over a year ago

do i use T2 and V3? not "T:T" and "V:V"?

user4039065 Over a year ago

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.

pucktack Over a year ago

im now getting application-defined or object defined error

pucktack Over a year ago

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

user4039065 Over a year ago

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?

|

Collectives™ on Stack Overflow

Sorting using VBA

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related