Tách danh sách tổng hợp ra nhiều danh sách khác

[khoa_pr]

[h=2]Tách danh sách tổng hợp ra nhiều danh sách khác[/h]

Chào các bạn!
Tôi có File Tach ds lop.xls. trong đó có nhiều Sheet: DS tong hop và các sheet khác là tên các lớp cần tách. nhờ các bạn giúp tôi viết Code VBA (tôi đã làm được bằng các hàm Index, Match . . ) theo yêu cầu sau:
1- Sheet DS tong hop : có 4 cột: STT, Họ và tên, giới tình Nữ, Lớp. Tạo một CommandButton tên Tách lớp.

2- ở các sheet lớp: lớp 61, Lớp 62, Lớp 63, Lớp 71 . . . Tạo nội dung sẵn như bảng bên dưới. khi nhập vào Cell tên lớp (D1) là 61 hoặc 62 hoặc 71. . . Khi nhấn Tách lớp thì code tách lớp sẽ căn cứ vào cột Lớp bên DS tong hop (cột D) để lọc toàn bộ những người có tên thuộc lớp đó và các cột dữ liệu có liên quan (như giới tính Nữ) để đưa qua sheet lớp phù hợp.

3- Cột số thứ tự tự động đánh lại từ 1 đến hết, tự động kẻ khung (Border) theo toàn bộ danh sách.
Rất mong được sự giúp đỡ của các bạn. ​

 

[dhn46]

Bạn xem như thế này có được không?

 

[concogia]

Tách danh sách tổng hợp ra nhiều danh sách khác

Chào các bạn!
Tôi có File Tach ds lop.xls. trong đó có nhiều Sheet: DS tong hop và các sheet khác là tên các lớp cần tách. nhờ các bạn giúp tôi viết Code VBA (tôi đã làm được bằng các hàm Index, Match . . ) theo yêu cầu sau:
1- Sheet DS tong hop : có 4 cột: STT, Họ và tên, giới tình Nữ, Lớp. Tạo một CommandButton tên Tách lớp.

2- ở các sheet lớp: lớp 61, Lớp 62, Lớp 63, Lớp 71 . . . Tạo nội dung sẵn như bảng bên dưới. khi nhập vào Cell tên lớp (D1) là 61 hoặc 62 hoặc 71. . . Khi nhấn Tách lớp thì code tách lớp sẽ căn cứ vào cột Lớp bên DS tong hop (cột D) để lọc toàn bộ những người có tên thuộc lớp đó và các cột dữ liệu có liên quan (như giới tính Nữ) để đưa qua sheet lớp phù hợp.

3- Cột số thứ tự tự động đánh lại từ 1 đến hết, tự động kẻ khung (Border) theo toàn bộ danh sách.
Rất mong được sự giúp đỡ của các bạn. ​

Nếu đã tạo nút "Tach lop" thì chỉ cần 1 sheet lấy danh sách từng lớp, còn đã tạo các sheet như trong file của bạn thì không cần nút bấm nữa, chỉ cần chọn sheet nào thì code sẽ lọc danh sách lớp đó cho bạn, "phẻ re"

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        If ActiveSheet.Name <> "DS tong hop" Then
            [a3:d100].Delete
                With Sheets("DS tong hop").Range(Sheets("DS tong hop").[a3], Sheets("DS tong hop").[a10000].End(xlUp)).Resize(, 4)
                    .AutoFilter 4, Val(Right(ActiveSheet.Name, 2))
                    .SpecialCells(12).Copy [a3]
                    .AutoFilter
                End With
            Range([a4], [a100].End(xlUp)) = [row(A:A)]
        End If
End Sub

Chép code này vào ThisWookbook là....xong
Thân

 

[khoa_pr]

chào bạn dhn46, cách bạn làm tôi thấy vẫn còn thủ công, ý tôi muốn nói là sau khi lọc danh sách qua các lớp số thứ tự cũng tự động tạo luôn, chứ không phải dùng công thức bạn ạ, hoặc là viết code để nó gán công thức vào cột STT luôn chứ đừng tạo công thức ăắng cách thủ công trong cột STT.

 

[khoa_pr]

chào bạn concogia
Giống như ý bạn, tôi chỉ muốn tạo 2 sheet thôi: 1 DS tong hop, 1 sheet Lop. sheet Lop cell D1 bỏ trống và tên sheet la "Lop . . " mà thôi và cho Ẩn (Hide) đi. Tôi đã viết được code để copy sheet Lop khi ta Click nút Tach lop và khi nhập tên lớp vào cell D1 ví dụ như 61, 62 . . . thì tên sheet "Lop" sẽ được ghép với cell D1 thành "Lop 61", "Lop 62" . . . luc bấy giờ code tách lớp sẽ căn cứ vào cell D1 và tên của Sheet để tách danh sách tổng hợp ra theo sheet lớp đã được copy và đổi tên. Bản thân không có ý tạo sẵn nhiều lớp. vì mỗi giáo viên chỉ dạy một vài lớp trong cùng một khối mà thôi, nên không thể tạo sẵn tất các các lớp trong cùng một khối.
Tóm lại chỉ cần 1 sheet "DS tong hop", 1 sheet "Lop". Khi nhập tên lớp vào D1 (61 hoặc 62,. . . ) và đổi tên sheet thành "Lop 61", "Lop 62", . . . Nhấn nút Tach lop thì sẽ tách tên HS và các dữ liệu có liên quan để đưa sang sheet có cùng tên với cột "lớp" bên "DS tong hop".

 

[TrangTho]

TachLop

Chào Anh khoa_pr
Em thấy anh giải quyết bài toán Tách lớp thật là hay. Em là người mới học. Xin anh post len cho em xem để học tập với.
Chào Anh, cảm ơn anh nhiều.

 

[SA_DQ]

(hú Cò Già này thiệt tình!

Thật ra không cần đổi tên trang tính đâu tác giả topic à! Chỉ cần đổi tiêu đề trang tính là được;

Trong file mình lấy [F2] làm ô chọn lớp, chứ không xài nút lệnh. Bạn hãy thử xem có chi chưa thấu lí đạt tình?

 

[khoa_pr]

Chào Hyen17
Cám ơn bạn. Tuy nhien như đã nói vì đây là thao tác tự động tách tên theo lớp, nên mình cũng muốn nhờ các bạn cho nó tự động đánh lại STT và kẻ Border luôn. Theo cách của bạn cũng hay nhưng chưa tự động STT và Border bạn ạ.

 

[concogia]

Chào Hyen17
Cám ơn bạn. Tuy nhien như đã nói vì đây là thao tác tự động tách tên theo lớp, nên mình cũng muốn nhờ các bạn cho nó tự động đánh lại STT và kẻ Border luôn. Theo cách của bạn cũng hay nhưng chưa tự động STT và Border bạn ạ.

Thật ra dạng bài này trên diễn đàn có rất nhiều rồi, chỉ hơi lạ là yêu cầu của bạn. Dữ liệu chính là danh sách học sinh toàn trường, muốn truy xuất một lớp nào đó để in hoặc muốn làm cái chi chi đó thì chỉ cần 1 sheet nữa là đủ, tha hồ mà tách tự động hay bấm nút tách cũng được.
Tạm thời mình làm cho bạn như thế này:
Danh sách học sinh bạn cứ thoải mái thêm vào tại sheet "DS Tong Hop"
1)- Chọn sheet "KhongDungNut", chọn lớp ở cell [D3] code sẽ tự động lọc lớp + Đánh số thứ tự + Kẻ khung cho bạn
( Tạo chọn lớp ở sheet "DS Tong Hop" cũng được nhưng mình thấy nó làm sao í )
2)- Chọn sheet "DungNut", chọn lớp ở cell [D3], bấm nút, code cũng sẽ làm các việc như Không Dùng Nút
Mình nghiêng về phương án "Không Dùng Nút"
Thân

 

[khoa_pr]

Chào concogia và tất cả các bạn tham gia diễn đàn!
Chắc các bạn không đọc kỹ bài tôi gởi lên nên mới chưa hiểu hết ý của tôi, nay tôi gởi lại File có bổ sung 2 cột (Ngày sinh, chổ ở) và nói rõ hơn ý của mình:
1- Các bạn thấy ở sheet DS tong hop tôi chỉ làm 6 cột, trong đó cột Lớp chỉ là cột điều kiện để biết những người có tên trong "DS tong hop" thuộc lớp nào, để khi tách chỉ tách họ tên những người đó cùng với nội dung 3 cột (giới tính, ngày sinh, chổ ở) mà thôi, đừng lấy nội dung trong cột Lớp.

2- Ở các sheet Lop ví dụ như Lop 61, Lop 62, . . . các bạn thấy tôi tạo sẵn 10 cột, nhưng không có cột Lớp. chỉ lặp lại cột STT, Họ và tên và Nữ.
3- Yêu cầu:
a/ Đối với việc tách DS: Sau khi lọc DS tong hop chỉ lấy nội dung của cột Họ và tên, Nữ để đưa qua các sheet Lop với điều kiện Cell D1(tên lớp) phải nhập vào tên lớp (61, 62, 63, . . .). nêu Cell D1 bỏ trống thì không tách Họ và tên, Nữ của "DS tong hop". Tự động đánh lại STT, kẻ Border cho cả danh sách (10 cột). Một giáo viên có phải dạy nhiều lớp từ bậc Trung học cơ sở trở lên, khác với mầm non và tiểu học. do đó tôi phải tạo nhiều Sheet Lop là như vậy chứ không nên tạo một sheet DS tong hop và 1 sheet Lop nhu các bạn nói. mỗi 1 sheet lớp có cả DS và điểm cần phải lưu lại.
b/ Cách thực hiện: các bạn có thể viết code giúp tôi thực hiện theo 2 cách:
- Với Code: dùng phương thức tìm kiếm hoặc dùng AutoFilter bằng cách nào cũng được, nhưng khi copy dữ liệu từ "DS tong hop" qua "Lop ..." thì chỉ copy nội dung (Từ A4 trở xuống) từ DS tong hop chứ đừng copy cả dòng tiêu đề (Từ A3: STT, B: Họ và tên, . . .) và dứt khoát không copy nội dung của cột Lớp (D). Sau khi có tên học sinh, giới tính Nữ bên các sheet Lop 61, Lop 62 . . . tôi chỉ việc nhập Ngày sinh, chổ ở (2 cột này có thể kẻ thêm bên "DS tong hop") và nhập điểm.
- Với việc thực hiện: Giống như ban concogia nói có thể dùng nút Tach hoặc dùng phương thức Worksheet_Activate cũng được với điều kiện như đã nêu ở mục a (Yêu cầu). xin các bạn hướng dẫn lại cả 2 cách thực hiện nhé để tôi nghiên cứu học hỏi thêm cho dầy đủ.

 

[khoa_pr]

sao không ai giúp mình giải quyết vấn đề đặt ra hết vậy

 

[concogia]

Chào concogia và tất cả các bạn tham gia diễn đàn!
Chắc các bạn không đọc kỹ bài tôi gởi lên nên mới chưa hiểu hết ý của tôi, nay tôi gởi lại File có bổ sung 2 cột (Ngày sinh, chổ ở) và nói rõ hơn ý của mình:
1- Các bạn thấy ở sheet DS tong hop tôi chỉ làm 6 cột, trong đó cột Lớp chỉ là cột điều kiện để biết những người có tên trong "DS tong hop" thuộc lớp nào, để khi tách chỉ tách họ tên những người đó cùng với nội dung 3 cột (giới tính, ngày sinh, chổ ở) mà thôi, đừng lấy nội dung trong cột Lớp.

2- Ở các sheet Lop ví dụ như Lop 61, Lop 62, . . . các bạn thấy tôi tạo sẵn 10 cột, nhưng không có cột Lớp. chỉ lặp lại cột STT, Họ và tên và Nữ.
3- Yêu cầu:
a/ Đối với việc tách DS: Sau khi lọc DS tong hop chỉ lấy nội dung của cột Họ và tên, Nữ để đưa qua các sheet Lop với điều kiện Cell D1(tên lớp) phải nhập vào tên lớp (61, 62, 63, . . .). nêu Cell D1 bỏ trống thì không tách Họ và tên, Nữ của "DS tong hop". Tự động đánh lại STT, kẻ Border cho cả danh sách (10 cột). Một giáo viên có phải dạy nhiều lớp từ bậc Trung học cơ sở trở lên, khác với mầm non và tiểu học. do đó tôi phải tạo nhiều Sheet Lop là như vậy chứ không nên tạo một sheet DS tong hop và 1 sheet Lop nhu các bạn nói. mỗi 1 sheet lớp có cả DS và điểm cần phải lưu lại.
b/ Cách thực hiện: các bạn có thể viết code giúp tôi thực hiện theo 2 cách:
- Với Code: dùng phương thức tìm kiếm hoặc dùng AutoFilter bằng cách nào cũng được, nhưng khi copy dữ liệu từ "DS tong hop" qua "Lop ..." thì chỉ copy nội dung (Từ A4 trở xuống) từ DS tong hop chứ đừng copy cả dòng tiêu đề (Từ A3: STT, B: Họ và tên, . . .) và dứt khoát không copy nội dung của cột Lớp (D). Sau khi có tên học sinh, giới tính Nữ bên các sheet Lop 61, Lop 62 . . . tôi chỉ việc nhập Ngày sinh, chổ ở (2 cột này có thể kẻ thêm bên "DS tong hop") và nhập điểm.
- Với việc thực hiện: Giống như ban concogia nói có thể dùng nút Tach hoặc dùng phương thức Worksheet_Activate cũng được với điều kiện như đã nêu ở mục a (Yêu cầu). xin các bạn hướng dẫn lại cả 2 cách thực hiện nhé để tôi nghiên cứu học hỏi thêm cho dầy đủ.

Bài của bạn không khó nhưng yêu cầu hơi...lạ
Bi giờ, nói lại nhé:
Ớ sheet "DS tong hop" có cái nút, tuần tự công việc thế này
- Các sheet còn lại nhập tên lớp vào cell [D1]
- Quay lại sheet "DS tong hop" bấm cái nút một phát, nếu cell [D1] ở sheet nào có dữ liệu thì lọc dữ liệu ở sheet "Ds tong hop" sang sheet đó còn [D1] mà trống thì không lọc
- Dữ liệu chỉ lấy 3 cột Stt, Họ tên, Giới tính (cái này dễ ẹct)
- Đánh lại số thứ tự (cái này dễ ẹct)
- Kẻ khung (cái này cũng...dễ ẹct)
Bây giờ bạn cần nói lại là muốn làm theo kiểu nào chứ mình thấy:
1) Nhập dữ liệu ở [D1] các sheet chỉ hợp lý khi chỉ cần 1 sheet lấy dữ liệu còn nếu tạo mỗi sheet 1 lớp là không cần thiết ( nếu làm nhiều sheet tên sheet đã cho ta biết dữ liệu trong sheet đó là của lớp nào rồi )
2) Việc bấm cái nút mình thấy nó sao sao í
Túm lại, cuối cùng ý bạn muốn làm như thế nào thì nói rõ ,sẽ làm đúng theo ý bạn thôi mà
Híc
Thân

 

[tintam7251]

Nguyên văn bởi khoa_pr
chứ không nên tạo một sheet DS tong hop và 1 sheet Lop nhu các bạn nói. mỗi 1 sheet lớp có cả DS và điểm cần phải lưu lại.

-Mục đích của bạn là làm sổ điểm cho từng lớp. Nếu bạn nói rõ điều này từ đầu thì đã không làm mất thì giờ công sức của nhiều người.
-Từ một danh sách tổng, lọc từng lớp vào từng sheet đã tạo sẵn thì cần gì phải viết code, bạn chỉ cần AutoFilter, copy và dán vào là xong.

 

[lehoanganhduc]

bạn chọn lệnh lặp for để nó chạy. cho biến chạy từ ô đầu tiên đến cuối cùng, và khi nhận được giá trị giống nhau thì cùng gán cho 1 sheet định trước.

 

[concogia]

Ok. Làm hoàn toàn theo ý bạn nhé

Mình làm theo ý bạn hoàn toàn
Chỉ có một chỗ chưa biết sử lý ra sao:
Trường hợp ở sheet nào đó đang có tí dữ liệu, nếu bạn xóa [D1] đi thì mình có "thịt" luôn dữ liệu ở sheet đó không bạn ?
Tạm thời mình làm thế này, cứ ở sheet nào mà [D1] có dữ liệu thì lọc theo [D1], còn không có thì "thịt" tuốt tuồn tuột nhé bạn ( nếu không thích có thể chỉnh lại)
Khuyến mãi cho bạn cell Sĩ số luôn

Public Sub TachTach()
    Dim Vung, Ws
    Application.ScreenUpdating = False
        Set Vung = Sheets("DS tong hop").Range(Sheets("DS tong hop").[a3], Sheets("DS tong hop").[a1000].End(xlUp))
        Sheets("DS tong hop").Columns("D:F").EntireColumn.Hidden = True
            For Each Ws In Worksheets
                If Ws.Name <> "DS tong hop" Then
                    Ws.[a4:j100].Delete: Ws.[f1].ClearContents
                        If Ws.[d1] <> vbNullString Then
                            With Vung.Resize(, 6)
                                .AutoFilter 6, Ws.[d1]
                                .Offset(1).SpecialCells(12).Copy Ws.[a4]
                                .AutoFilter
                            End With
                                Ws.Range(Ws.[a4], Ws.[a100].End(xlUp)) = [row(A:A)]
                                Ws.Range(Ws.[a4], Ws.[a100].End(xlUp)).Resize(, 10).Borders.Weight = xlThin
                                Ws.[f1] = Ws.[a100].End(xlUp).Row - 3
                        End If
                End If
            Next Ws
        Sheets("DS tong hop").Cells.EntireColumn.Hidden = False
    Application.ScreenUpdating = True
End Sub

Thân

 

[khoa_pr]

Chào concogia !
Nhờ bạn giải thích thêm các đoạn code mà mình đánh số:
1. Set Vung = Sheets("DS tong hop").Range(Sheets("DS tong hop").[a3], Sheets("DS tong hop").[a1000].End(xlUp))
2. Sheets("DS tong hop").Columns("D:F").EntireColumn.Hidden = True
For Each Ws In Worksheets
If Ws.Name <> "DS tong hop" Then
Ws.[a4:j100].Delete: Ws.[f1].ClearContents
If Ws.[d1] <> vbNullString Then
7. With Vung.Resize(, 6)
8. .AutoFilter 6, Ws.[d1]
9. .Offset(1).SpecialCells(12).Copy Ws.[a4]
.AutoFilter
End With
Ws.Range(Ws.[a4], Ws.[a100].End(xlUp)) = [row(A:A)]
Ws.Range(Ws.[a4], Ws.[a100].End(xlUp)).Resize(, 10).Borders.Weight = xlThin
Ws.[f1] = Ws.[a100].End(xlUp).Row - 3
End If
End If
Next Ws
Sheets("DS tong hop").Cells.EntireColumn.Hidden = False
Dòng số 1: vung có phải là là từ A3 : A1000 không? vung có khác gì CurentRegion tại sao không viết Range(Sheets("DS tong hop").[A3:A1000].End(xlUp))
Dòng số 2: Cột F (Lớp) là cột điều kiện để AutoFilter tại sao lại hidden, như vậy AutoFilter sẽ dựa vào đâu.(mình thật sự không hiểu)
Dòng số 7: With Vung.Resize(, 6) 'có phải nó lấy cột số 1 (A) làm chuẩn rồi dịch chuyển sang phải đến cột 6 ?
Dòng số 8: .AutoFilter 6, Ws.[d1] 'Như đã nói ở dòng số 2 AutoFilter 6 trong khi côột 6 (F) ( thì cột 6 (F) bị ẩn mất rồi chả lẽ Hidden vẫn có thể lọc được hả bạn?
Dòng 9: .Offset(1).SpecialCells(12).Copy Ws.[a4] 'Code này có phải là sau khi lọc nó sẽ copy và paste sang các sheet khác từ cột thứ nhất (Offset(1)) và từ dòng thứ tư (Ws.[a4]) ? có phải lúc này cột F vẫn ẩn phải không bạn.
như vậy việc bạn cho ẩn D:F là để nó không copy nội dung cột F sang các sheet Lop có phải không?
Cám ơn bạn một lần nữa.