Hỏi về code Sort trong vba mà không cần đổi Sheet (1 người xem)

[datvocnstu]

Cho e hỏi e đang có 2 sheet A và B,
E đang chọn Sheet A/ Chạy 1 User form tạo thêm dữ liệu ở Sheet B -> cần Sort lại sheet B và Filldown 1 số cột dữ liệu, mà không cần chọn qua sheet B, vẫn giữ nguyên Sheet A thì code sau e cần thêm gì ạ
E cảm ơn.

Sub Sort_dulieuhangton()

    Dim iRow_ht As Long
      
    
    
    iRow_ht = [Counta(SheetB!B:B)] + 2
    
    
    ActiveWorkbook.Worksheets("SheetB").Select
    
    Range("A3:L" & iRow_ht).Select
 
    ActiveWorkbook.Worksheets("SheetB").Sort.SortFields.Clear
    
    ActiveWorkbook.Worksheets("SheetB").Sort.SortFields.Add2 Key:=Range( _
        "L4:L" & iRow_ht), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "304,316,430,201", DataOption:=xlSortTextAsNumbers
    
    ActiveWorkbook.Worksheets("SheetB").Sort.SortFields.Add2 Key:=Range( _
        "B4:B" & iRow_ht), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "Tròn,Vuông,Hop,Láp,La,V, DataOption:=xlSortNormal"
    
    ActiveWorkbook.Worksheets("SheetB").Sort.SortFields.Add2 Key:=Range( _
        "J4:J" & iRow_ht), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    
    ActiveWorkbook.Worksheets("SheetB").Sort.SortFields.Add2 Key:=Range( _
        "K4:K" & iRow_ht), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    
    With ActiveWorkbook.Worksheets("SheetB").Sort
        .SetRange Range("A3:L" & iRow_ht)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    
    End With
 
    
    Worksheets("SheetB").Range("F4:F" & iRow_ht).FillDown
    
    Worksheets("SheetB").Range("G4:G" & iRow_ht).FillDown
    
    Worksheets("SheetB").Range("H4:H" & iRow_ht).FillDown
    
    Worksheets("SheetB").Range("J4:J" & iRow_ht).FillDown
    
    Worksheets("SheetB").Range("K4:K" & iRow_ht).FillDown
    
    Worksheets("SheetB").Range("A4:A" & iRow_ht).FillDown
    
    Worksheets("SheetB").Range("L4:L" & iRow_ht).FillDown
  
    
End Sub

 

[Châu Bá Thông]

Các hạ thử chỉnh chút lại xem:

Sub Sort_dulieuhangton()
Dim iRow_ht As Long: iRow_ht = [Counta(SheetB!B:B)] + 2
Dim iSH As Worksheet: Set iSH = ActiveWorkbook.Worksheets("SheetB")
Dim iRange As Range: Set iRange = iSH.Range("A3:L" & iRow_ht)

With iSH
  .Sort.SortFields.Clear
  .Sort.SortFields.Add2 Key:=Range("L4:L" & iRow_ht), _
          SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
          "304,316,430,201", DataOption:=xlSortTextAsNumbers
      
  .Sort.SortFields.Add2 Key:=Range("B4:B" & iRow_ht), _
          SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
          "Tròn,Vuông,Hop,Láp,La,V, DataOption:=xlSortNormal"
      
  .Sort.SortFields.Add2 Key:=Range("J4:J" & iRow_ht), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      
  .Sort.SortFields.Add2 Key:=Range("K4:K" & iRow_ht), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With

With iSH.Sort
  .SetRange iRange
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
 
With iSH
.Range("A4:A" & iRow_ht).FillDown
.Range("F4:F" & iRow_ht).FillDown
.Range("G4:G" & iRow_ht).FillDown
.Range("H4:H" & iRow_ht).FillDown
.Range("J4:J" & iRow_ht).FillDown
.Range("K4:K" & iRow_ht).FillDown
.Range("L4:L" & iRow_ht).FillDown
End With
End Sub

 

[batman1]

With iSH
.Range("A4:A" & iRow_ht).FillDown
.Range("F4:F" & iRow_ht).FillDown
.Range("G4:G" & iRow_ht).FillDown
.Range("H4:H" & iRow_ht).FillDown
.Range("J4:J" & iRow_ht).FillDown
.Range("K4:K" & iRow_ht).FillDown
.Range("L4:L" & iRow_ht).FillDown
End With

iSH.Range("F4:H" & iRow_ht & ",J4:L" & iRow_ht & ",A4:A" & iRow_ht).FillDown

 

[datvocnstu]

Các hạ thử chỉnh chút lại xem:

Sub Sort_dulieuhangton()
Dim iRow_ht As Long: iRow_ht = [Counta(SheetB!B:B)] + 2
Dim iSH As Worksheet: Set iSH = ActiveWorkbook.Worksheets("SheetB")
Dim iRange As Range: Set iRange = iSH.Range("A3:L" & iRow_ht)

With iSH
  .Sort.SortFields.Clear
  .Sort.SortFields.Add2 Key:=Range("L4:L" & iRow_ht), _
          SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
          "304,316,430,201", DataOption:=xlSortTextAsNumbers
     
  .Sort.SortFields.Add2 Key:=Range("B4:B" & iRow_ht), _
          SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
          "Tròn,Vuông,Hop,Láp,La,V, DataOption:=xlSortNormal"
     
  .Sort.SortFields.Add2 Key:=Range("J4:J" & iRow_ht), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
     
  .Sort.SortFields.Add2 Key:=Range("K4:K" & iRow_ht), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With

With iSH.Sort
  .SetRange iRange
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
 
With iSH
.Range("A4:A" & iRow_ht).FillDown
.Range("F4:F" & iRow_ht).FillDown
.Range("G4:G" & iRow_ht).FillDown
.Range("H4:H" & iRow_ht).FillDown
.Range("J4:J" & iRow_ht).FillDown
.Range("K4:K" & iRow_ht).FillDown
.Range("L4:L" & iRow_ht).FillDown
End With
End Sub

Vâng thanks bác, ổn định rồi ạ