Trích lọc dự liệu sang sheet khác

tranquangdat · 11/2/11

Kính gởi các anh, chị

Em là thành viên mới của giải pháp excell, cung là người mới tập tành học excell. Em có một file Excell thế này (có gởi kèm) mong anh, chị giúp đỡ. Nói thật lòng em làm hơn bốn tháng nay chưa được. Mong anh, chị giúp em điều này nhé.
Trong sheet DS.CDEN và sheet DS.CDI em có sử dụng VBA hay là Macro gì đó để chèn dòng tự động. Anh, chị giúp chỉnh sửa lại Macro này giùm em là: Khi nhấp đúp vào một ô cuối của cột B (B8) thì Excell sẽ tự động chèn dòng. Ngoài ra nhấp đúp vào các ô khác thì không chèn giống như file của em. Có cách nào chỉ cần một Sheet có Macro mà áp dụng cho cả hai Sheet (sheet DS.CDEN và sheet DS.CDI ) không anh?.
Trong file em có Sheet TH, em muốn lọc một cách tự động có điều kiện dữ liệu từ Sheet DS.CDEN và sheet DS.CDI vào sheet TH này với điều kiện:
1) Nếu ở ô C1 Sheet TH em chọn DEN; Ô E1 sheet TH em chọn một năm (2010) thì Excell sẽ lọc danh sách những em học sinh từ Sheet DS.CDEN cột “ngày tháng năm chuyển đến” có năm chuyển đến đã chọn ở ô E1 Sheet TH sang sheet TH và dán vào ô A4.
VD: Sheet TH ô C1 = DEN; ô E1 = 2010
Excell sẽ lọc những em trong DS.CDEN và có ngày tháng năm chuyển là 2010 sang Sheet TH bắt đầu tại ô A4.
2) Nếu em chọn ô C1 Sheet TH = ĐI; Ô E1 sheet TH em chọn một năm bất kỳ (2011) thì Excell sẽ lọc những em trong DS.CDI và cò ngày tháng năm chuyển là 2011 sang Sheet TH Bắt đầu từ ô A4.
* Nếu em chọn năm khác thì Excell sẽ xóa những năm đã dán ở Sheet TH để dán những em thỏa điều kiện đã chọn ở ô C1 và E1 Sheet TH vào. Nếu em không chọn thì chỉ có tiêu đề.
* Nếu ở Sheet DS.CDEN và DS.CDI có cập nhật thêm học sinh mới theo năm thì khi chọn ở Sheet TH Excell cũng sẽ tự động cập nhật và hiện thị.
Có công thức nào làm được điều đó không anh, chị. Nếu không có thì phải sử dụng Macro hay VBA thì phải viết như thế nào? Mong anh, chị giúp đỡ cho em. Nếu được nó sẽ giúp đỡ cho em rất nhiều, không chỉ có file đính kèm mà còn áp dụng vào rất nhiều file khác.
Nếu được có thể gởi qua giúp em: Dat76@ymail.com
Chúc anh, chị trong diễn đàn vui, khỏe và thành đạt. Chân thành cảm ơn anh, chị nhiều nhiều.

MinhCong · 11/2/11

Bạn dùng tạm đoạn code sau nhé!

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$1" Or Target.Address = "$E$1" Then
     Range("A3").CurrentRegion.Clear
     With IIf(Len(Range("C1")) = 2, Sheets("DS.CDI"), Sheets("DS.CDEN")).Range("A2").CurrentRegion.Offset(1)
        .AutoFilter Field:=4, Criteria1:=">=01/01/" & Range("E1"), Operator:=xlAnd, Criteria2:="<=01/01/" & Range("E1") + 1
        .SpecialCells(12).Copy Range("A3")
        .AutoFilter
     End With
  End If
End Sub

tranquangdat · 11/2/11

Mình sẽ làm. Cảm ơn bạn nhiều nhé

concogia · 11/2/11

MinhCong đã viết:

Bạn dùng tạm đoạn code sau nhé!

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" Or Target.Address = "$E$1" Then
Range("A3").CurrentRegion.Clear
With IIf(Len(Range("C1")) = 2, Sheets("DS.CDI"), Sheets("DS.CDEN")).Range("A2").CurrentRegion.Offset(1)
.AutoFilter Field:=4, Criteria1:=">=01/01/" & Range("E1"), Operator:=xlAnd, Criteria2:="<=01/01/" & Range("E1") + 1
.SpecialCells(12).Copy Range("A3")
.AutoFilter
End With
End If
End Sub

Phải thêm một code cho sự kiện Worsheet Activate nữa chứ Minh Cong, bạn í muốn khi cập nhật thêm danh sách ở 2 sheet kia thì sheet TH cũng cập nhật theo mà
Trốn ở đâu lâu quá "zị" bồ tèo

MinhCong · 11/2/11

concogia đã viết:
Phải thêm một code cho sự kiện Worsheet Activate nữa chứ Minh Cong, bạn í muốn khi cập nhật thêm danh sách ở 2 sheet kia thì sheet TH cũng cập nhật theo mà
Trốn ở đâu lâu quá "zị" bồ tèo

Em cũng quên cái vụ tự động cập nhật nữa chứ. Không biết như dzì có đúng hông nữa!

Mã:

Private Sub Worksheet_Activate()
Range("A3").CurrentRegion.Clear
     With IIf(Len(Range("C1")) = 2, Sheets("DS.CDI"), Sheets("DS.CDEN")).Range("A2").CurrentRegion.Offset(1)
        .AutoFilter Field:=4, Criteria1:=">=01/01/" & Range("E1"), Operator:=xlAnd, Criteria2:="<=01/01/" & Range("E1") + 1
        .SpecialCells(12).Copy Range("A3")
        .AutoFilter
     End With
End Sub

Lâu nay công việc lu bu cuối năm nên ít lên diễn đàn. Anh vẫn khỏe chứ?

tranquangdat · 11/2/11

Cảm ơn anh Minh Công nhé!
Nhưng anh giúp em thêm chút nữa nhé.
Em chỉ muốn copy những dòng thoả mãn điều kiện trong sheet TH và dán vao sheet TH bắt đầu từ o A4. Còn ở các sheet DS.DEN và DS.DI vẫn giữ nguyên không thay đổi, không bị lọc lại và nổi các nút lọc. Giúp em nhé.

MinhCong · 11/2/11

tranquangdat đã viết:
Cảm ơn anh Minh Công nhé!
Nhưng anh giúp em thêm chút nữa nhé.
Em chỉ muốn copy những dòng thoả mãn điều kiện trong sheet TH và dán vao sheet TH bắt đầu từ o A4. Còn ở các sheet DS.DEN và DS.DI vẫn giữ nguyên không thay đổi, không bị lọc lại và nổi các nút lọc. Giúp em nhé.

Bạn xem đoạn code trong sheet TH những chỗ nào có chữ Range("A3") Bạn thay nó bằng Range("A4") là được thôi. Còn những sheet khác thì nó có bị thay đổi gì đâu, vẫn như cũ của Bạn kia mà.

tranquangdat · 11/2/11

Cảm ơn anh Minh Công nhé!
Tuyệt quá anh Công ơi! Giúp em thêm tý nữa nhé.
Ở sheet TH ô C1 em có chọn DS.CDEN hay DS.CDI thì nó cũng lọc từ DS.CDEN để dán vào sheet TH. Anh giúp em cho nó lọc Khi em chọn DS.CDEN ở sheet TH thì nó lọc ở sheet DS.CDEN còn em chọn DS.CDI ở sheet TH thì nó lọc ở sheet DS.CDI. Còn cái này nữa Khi chọn xong năm ở ô E1 sheet TH thì nó lọc và hiện kết quả luôn không cần chuyển qua lại giữa các sheet nó mới hiện kết quả lọc. Cảm ơn anh nhiều. Có lẽ em làm phiền anh hơi nhiều,đừng buồn anh nhé

MinhCong · 11/2/11

tranquangdat đã viết:
Cảm ơn anh Minh Công nhé!
Tuyệt quá anh Công ơi! Giúp em thêm tý nữa nhé.
Ở sheet TH ô C1 em có chọn DS.CDEN hay DS.CDI thì nó cũng lọc từ DS.CDEN để dán vào sheet TH. Anh giúp em cho nó lọc Khi em chọn DS.CDEN ở sheet TH thì nó lọc ở sheet DS.CDEN còn em chọn DS.CDI ở sheet TH thì nó lọc ở sheet DS.CDI. Còn cái này nữa Khi chọn xong năm ở ô E1 sheet TH thì nó lọc và hiện kết quả luôn không cần chuyển qua lại giữa các sheet nó mới hiện kết quả lọc. Cảm ơn anh nhiều. Có lẽ em làm phiền anh hơi nhiều,đừng buồn anh nhé

Ý Bạn là sao nhỉ?
Bạn cứ chọn ở ô C1 là Đi hoặc Đến thì nó sẽ tự động lọc dữ liệu bên sheet DS.CDI (nếu bạn chọn ô C1 = Đi) còn lọc bên sheet DS.CDEN (nếu ô C1 = Đến) cũng tương tự như ô E1 Bạn chọn năm nó cũng vậy sẽ lọc tự động theo sheet (Tức khi Bạn chọn năm ở ô E1 thì ô C1 lúc này đã có giá trị là Đi hoặc Đến -> Nó sẽ tự tìm đến sheet DS.CDI hoặc sheet DS.CDEN để lọc). Bạn hãy bấm thử lại đi, tải cái file mà Tôi gởi lên sau cùng (sau bài của anh Concogia ấy, trong sheet TH có 2 sự kiện là: Worksheet_Activate() và Worksheet_Change(ByVal Target As Range))

Mã:

Private Sub Worksheet_Activate()
Range("A3").CurrentRegion.Clear
     With IIf(Len(Range("C1")) = 2, Sheets("DS.CDI"), Sheets("DS.CDEN")).Range("A2").CurrentRegion.Offset(1)
        .AutoFilter Field:=4, Criteria1:=">=01/01/" & Range("E1"), Operator:=xlAnd, Criteria2:="<=01/01/" & Range("E1") + 1
        .SpecialCells(12).Copy Range("A3")
        .AutoFilter
     End With
End Sub
-------------
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$1" Or Target.Address = "$E$1" Then
     Range("A3").CurrentRegion.Clear
     With IIf(Len(Range("C1")) = 2, Sheets("DS.CDI"), Sheets("DS.CDEN")).Range("A2").CurrentRegion.Offset(1)
        .AutoFilter Field:=4, Criteria1:=">=01/01/" & Range("E1"), Operator:=xlAnd, Criteria2:="<=01/01/" & Range("E1") + 1
        .SpecialCells(12).Copy Range("A3")
        .AutoFilter
     End With
  End If
End Sub

Lê Duy Thương · 12/2/11

tranquangdat đã viết:
có công thức nào làm được điều đó không anh, chị
.

đương nhiên là công thức làm được chứ
nếu không muốn dùng vba thì công thức đây
xem thêm file

tranquangdat · 15/2/11

Cảm ơn anh Duy Thương và anh Minh Công nhé!
Anh Thương ơi. Tại sao khi em tải File từ diên đàn về nó cứ hiện tên file như vậy: (attachment.php.
PHP File 50KB) không sao đọc được. Ở Sheet DS.CDEN và DS.CDI em co doạn code chèn dòng như sau:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",COUNTA(R3C2:RC[1]))"
Application.CutCopyMode = False
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
ActiveCell.Offset(0, 0).Range("B2").Select
End Sub
Khi em nhấp đúp vào bất kỳ ô nào của dòng bất kỳ nào thì nó chèn thêm 1 dòng vào phía trên dòng đó. Anh giúp em chỉ khi nào nhấp vào ô trên dòng cuối của cột B thì mới chèn, còn lại các ô khác thì không không anh. Cảm ơn anh nhiều.

MinhCong · 15/2/11

Bạn thử sửa lại thành vầy xem sao:

Mã:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim enR As Long
enR = Range("B65500").End(xlUp).Row
If Selection.Row = enR + 1 Then
   Selection.EntireRow.Insert
   Cells(Selection.Row, 1).FillDown
End If
End Sub

Bạn có thể cho đoạn code trên vào 1 module sau đó vào mỗi sheet dùng sự kiện Worksheet_BeforeDoubleClick

Chẳng hạn code trong module

Mã:

Sub Chendong()
Dim enR As Long
enR = Range("B65500").End(xlUp).Row
If Selection.Row = enR + 1 Then
   Selection.EntireRow.Insert
   Cells(Selection.Row, 1).FillDown
End If
End Sub

code trong worksheet

Mã:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Chendong
End Sub

tranquangdat · 15/2/11

Anh Công ơi giúp em thêm chut nữa nha
Bây giờ em không nhấp đúp vào ô cuối cột B và chèn lên trên một dòng nữa mà em chỉ cần đánh chữ vào ô cuối cột B thì nó chèn xuống dưới một dòng và copy công thức ô cuối của cột A xuống. Cảm ơn anh nhiều

MinhCong · 15/2/11

tranquangdat đã viết:
Anh Công ơi giúp em thêm chut nữa nha
Bây giờ em không nhấp đúp vào ô cuối cột B và chèn lên trên một dòng nữa mà em chỉ cần đánh chữ vào ô cuối cột B thì nó chèn xuống dưới một dòng và copy công thức ô cuối của cột A xuống. Cảm ơn anh nhiều

Làm thế thì cũng được chỉ cần dùng sự kiện worksheet_change là được. Như trường hợp của Bạn sao không dùng công cụ có sẵn của excel cho nó khỏe cần gì phải dùng VBA -> Tức tạo creat list ấy -> Quét chọn vùng dữ liệu -> Bấm phải chuột -> Chọn creat list -> Lúc nàu chỉ cần nhập chữ ở ô cuối cùng trong vùng viềng màu xanh nó sẽ tự động copy công thức bên trên xuống, hãy thử xem nhé! Nếu không thích mà muốn dùng VBA thì đọi nhé! Giờ mình có công việc rồi.

tranquangdat · 15/2/11

Em dùng creat list nó nổi dường viền và mũi tên màu xanh nó xấu quá. có cách nào bỏ đi mà không ảnh hưởng không anh.

MinhCong · 15/2/11

tranquangdat đã viết:
Em dùng creat list nó nổi dường viền và mũi tên màu xanh nó xấu quá. có cách nào bỏ đi mà không ảnh hưởng không anh.

Bình thường thôi có gì đâu mà xấu, khi in ấn nó đâu có bị ảnh hưởng gì, quan trọng là công việc của mình được hoàn thành xuất sắc, nhanh chóng là ok.

Lê Duy Thương · 15/2/11

tranquangdat đã viết:
cảm ơn anh duy thương và anh minh công nhé!
Anh thương ơi. Tại sao khi em tải file từ diên đàn về nó cứ hiện tên file như vậy: (attachment.php.
Php file 50kb) không sao đọc được..

đoán . Hình như máy bạn bị thiếu winrar

tranquangdat · 16/2/11

Cảm ơn anh Minh Công và anh Duy Thương nhiều nhé!
Em biết làm phiền hai anh nhiều quá em cũng ngại lắm. Hai anh giúp em thêm lần nũa nhé (Em có gơi file đính kèm). Viết code có yều cầu như sau:
Hai sheet DS.DEN và DS.DI em muốn khi đánh tên vào ô B8 thì nó tự động kẻ khung bao các ô A8:F8 và copy công thức ở ô A7 xuống ô A8. Tương tự như vậy cho các ô A9,A,.....
Trong file em có thêm sheet Tim. Khi nhập tên cần tìm vào ô A3 (HSDEN) hoặc ô A9 (HS DI) nó sẽ hiện một vài thông tin của học sinh đó. Nếu em nhâp chuột vào ô C4 hoặc ô C10 thì con trỏ chuột sẽ nhảy tới DS.CDEN hoặc DS.CDI và nằm ở ô trong cột STT có STT bằng STT ở ô C4 (HSDEN) hoặc ô c10 (HSDI).
Một lần nữa cảm ơn các anh nhiều.

MinhCong · 16/2/11

Trong khi chờ đợi các cao thủ Bạn dùng tạm cái này vậy:
1/Cho cho sheet TIM

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$3" Then
  Range("B4:B6").ClearContents
    If Target = "" Then
        MsgBox "Hay nhap ten hoc sinh can tim"
    ElseIf Not Sheet1.Range("B:F").Find(Target, , xlValues, xlWhole) Is Nothing Then
        Range("B4") = Sheet1.Range("B:F").Find(Target, , xlValues, xlWhole).Offset(, -1)
        Range("B5") = Target
        Range("B6") = Sheet1.Range("B:F").Find(Target, , xlValues, xlWhole).Offset(, 1)
    Else
        MsgBox "Khong co ten hoc sinh nay trong sheet DS.CDEN"
    End If
  End If
  If Target.Address = "$A$9" Then
  Range("B4:B6").ClearContents
    If Target = "" Then
        MsgBox "Hay nhap ten hoc sinh can tim"
    ElseIf Not Sheet2.Range("B:F").Find(Target, , xlValues, xlWhole) Is Nothing Then
        Range("B10") = Sheet2.Range("B:F").Find(Target, , xlValues, xlWhole).Offset(, -1)
        Range("B11") = Target
        Range("B12") = Sheet2.Range("B:F").Find(Target, , xlValues, xlWhole).Offset(, 1)
    Else
        MsgBox "Khong co ten hoc sinh nay trong sheet DS.CDI"
    End If
  End If
End Sub

2/Code cho sheet DS.CDEN và DS.CDI như sau:

Mã:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim enR As Long
enR = Range("B65500").End(xlUp).Row
If Target.Address = Cells(enR + 1, 2).Address Then
   Cells(enR, 1).FillDown
End If
With Range("A" & enR & ":F" & enR)
    .Borders(xlEdgeLeft).Weight = xlThin
    .Borders(xlEdgeTop).Weight = xlHairline
    .Borders(xlEdgeBottom).Weight = xlHairline
    .Borders(xlEdgeRight).Weight = xlThin
    .Borders(xlInsideVertical).Weight = xlThin
End With
End Sub

concogia · 16/2/11

tranquangdat đã viết:
Cảm ơn anh Minh Công và anh Duy Thương nhiều nhé!
Em biết làm phiền hai anh nhiều quá em cũng ngại lắm. Hai anh giúp em thêm lần nũa nhé (Em có gơi file đính kèm). Viết code có yều cầu như sau:
Hai sheet DS.DEN và DS.DI em muốn khi đánh tên vào ô B8 thì nó tự động kẻ khung bao các ô A8:F8 và copy công thức ở ô A7 xuống ô A8. Tương tự như vậy cho các ô A9,A,.....
Trong file em có thêm sheet Tim. Khi nhập tên cần tìm vào ô A3 (HSDEN) hoặc ô A9 (HS DI) nó sẽ hiện một vài thông tin của học sinh đó. Nếu em nhâp chuột vào ô C4 hoặc ô C10 thì con trỏ chuột sẽ nhảy tới DS.CDEN hoặc DS.CDI và nằm ở ô trong cột STT có STT bằng STT ở ô C4 (HSDEN) hoặc ô c10 (HSDI).
Một lần nữa cảm ơn các anh nhiều.

Trong sheet "TIM", bạn truy vấn theo "TÊN" là không ổn, học sinh đông việc trùng tên họ là chắc chắn, theo mình, bạn nên tạo mã duy nhất rồi truy theo mã thì ổn hơn
Làm theo yêu cầu của bạn đây
Thân

Trích lọc dự liệu sang sheet khác

Thành viên mới

File đính kèm

Thành viên gắn bó

File đính kèm

Thành viên mới

Gội rồi mới Cạo

Thành viên gắn bó

File đính kèm

Thành viên mới

Thành viên gắn bó

Thành viên mới

Thành viên gắn bó

Cạo lấy gì gội (Dịch quá không gội được)

File đính kèm

Thành viên mới

Thành viên gắn bó

Thành viên mới

Thành viên gắn bó

Thành viên mới

Thành viên gắn bó

Cạo lấy gì gội (Dịch quá không gội được)

Thành viên mới

File đính kèm

Thành viên gắn bó

File đính kèm

Gội rồi mới Cạo

File đính kèm

Đếm ngược thời gian