VBA to sort particular column in ascending order after I get the data from another sheet

[ameerafi]

I need to get data from another worksheet with multiple criteria so I wrote a VBA. Now once I get all the required data in the new sheet "FI" then the Column D from D2 till last row has to be sorted in Ascending order. Please help me with the code.

/code

Private Sub CommandButton1_Click()

lastRow = Worksheets("E-Dashboard").Range("A" & Rows.Count).End(xlUp).Row

For r = 2 To lastRow

If (Worksheets("E-Dashboard").Range("AA" & r).Value = "1" Or Worksheets("E-Dashboard").Range("AA" & r).Value = "2" Or Worksheets("E-Dashboard").Range("AA" & r).Value = "3") Then

Worksheets("E-Dashboard").Range("B" & r & ":C" & r & ":D" & r & ":E" & r & ",G" & r & ",J" & r & ":K" & r & ":L" & r & ",AI" & r & ":AJ" & r & ":AK" & r).Copy

Worksheets("FI").Activate

lastrowFI = Worksheets("FI").Range("A" & Rows.Count).End(xlUp).Row

Worksheets("FI").Range("A" & lastrowFI + 1).Select

ActiveSheet.Paste

End If

Next r

End Sub

/code

 

[vcoolio]

Hello Ameerafi,

I hope you don't mind but I've re-written your code with the criteria placed in an array and then filtering on the array as follows:-

Private Sub CommandButton1_Click()

Dim wsE As Worksheet: Set wsE = Sheets("E-Dashboard")
Dim wsFI As Worksheet: Set wsFI = Sheets("FI")
Dim ar As Variant

ar = Array(1, 2, 3)
 
Application.ScreenUpdating = False

 For i = 0 To UBound(ar)
        With wsE.[A1].CurrentRegion
                .AutoFilter 27, ar(i)
                Union(.Columns("B:E"), .Columns("G"), .Columns("J:L"), .Columns("AI:AK")).Offset(1).Copy
                wsFI.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With
 Next i
  
wsFI.Range("A2", wsFI.Range("K" & wsFI.Rows.Count).End(xlUp)).Sort wsFI.[D2], 1

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

You should find this more efficient than your loop type code especially if you have a large data set.

The code will also sort the pasted data (entire rows) in Sheet FI based on Column D, ascending.

I hope that this helps.

Cheerio,
vcoolio.