Vba copy and paste filtered sorted data in new sheet

[mark84]

Hello,
I d want your support to solve this work.
I have a file named "Test Ven".
I tried to write a part of vba but it doesn't work.
I need a macro to automate these steps:
1) order all data for column B from A to Z
2) Then order these data for column C from the smallest to the greatest.
3) Filter for column Bfor the criteria "HP"
4) Copy and paste in column A of the new sheet named "Work1" filtered values of the columns A, C, D, E, F, G, H.
5) Then come back on the sheet "Test Ven" and filter for column B for the criteria "MP"
6) Copy and paste in "Work1" sheet after column G, filtered values of the columns G, H
7) Then come back on the sheet "Test Ven" and filter for column B for the criteria "GI"
8) Copy and paste in "Work1" sheet after column I, filtered values of the columns G, H
9) Then come back on the sheet "Test Ven" and filter for column B for the criteria "VS"
10) Copy and paste in "Work1" sheet after column K, filtered values of the columns G, H, J

Thanks so much for your support!

Mark

 

[Fluff]

How about

Sub mark()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("HP", "A1", "A:H", "MP", "I1", "G:H", "GI", "K1", "G:H", "VS", "M1", "G:J")
   With Sheets("Test Ven")
      .Range("a1").CurrentRegion.Sort .Range("B1"), xlAscending, .Range("C1"), , xlAscending, , , xlYes
      For i = 0 To UBound(Ary) Step 3
         .Range("A1:J1").AutoFilter 2, Ary(i)
         .AutoFilter.Range.Offset(1).Columns(Ary(i + 2)).Copy Sheets("Work1").Range(Ary(i + 1))
      Next i
      .AutoFilterMode = False
   End With
   Sheets("Work1").Range("B:B,O:O").EntireColumn.Delete
End Sub

This assumes that the Work1 sheet already exists

 

[mark84]

Thank you! It's perfect and works well!

Have a nice day

Mark

 

[Fluff]

You're welcome & thanks for the feedback.