Tách một số cột của bảng dữ liệu ra thành file riêng

[titanic20072007]

Mình đang tạo một tệp tính điểm cho giáo viên bộ môn trong trường. Trường mình yêu cầu sau mỗi học kỳ sau khi tính điểm TB cho HS xong thì GV bộ môn nộp tệp có STT, Họ tên HS, điểm TB của môn cho GV chủ nhiệm lớp tính để xếp loại HS (có chương trình cho chủ nhiệm riêng) và đưa dữ liệu lên web. Với những GV nhiều tuổi và không thạo thì copy và paste mất thời gian và khó khăn. Mình muốn khi nhấn vào nút Kết xuất tương ứng với học kỳ cần kết xuất thì dl của những cột đó tự động lưu ra một tệp. Nhưng mình lại không biết làm và mình không biết nhiều về VB.
Mình có gửi tệp mẫu và tập kết quả cần đạt được kèm theo nhờ các bác làm giúp. Mong các bạn giúp đỡ. Xin cảm ơn.

 

[titanic20072007]

Không có bác nào giúp đỡ em sao

 

[TrungChinhs]

Nếu chỉ có vậy thì cứ Copy nguyên Sheet Toán 7A sang file mới rồi rút các cột không cần đi là được mà. Viết code làm gì cho nó phức tạp (đừng có nói là những việc này các thày cũng không biết nốt nha).

 

[Quang_Hải]

Bạn xài tạm file này nha, làm theo cách củ chuối thôi. Bạn có thể mở code ra và điều chỉnh cho phù hợp với thực tế

 

[titanic20072007]

Cảm ơn bạn quanghai1969. Mình đã chạy thử tệp bạn gửi mình thấy cách làm của bạn là copy hết sang file mới rồi xóa đi những cột không cần thiết. Với cách này nếu cột TBmHK1 mà có công thức thì sau khi xóa đi các cột chứa dữ liệu thì cột này báo lỗi và nếu mình muốn tên của tệp mới được lấy từ ở ô C5 ở sheet HETHONG thì thay đổi lệnh như thế nào? Mình không hiểu về VB nên đọc chảng hiểu gì. Mong bạn chỉnh lại giúp. Mình gửi lại tệp chuẩn bạn xem giúp mình nhé.

 

[titanic20072007]

Nếu chỉ có vậy thì cứ Copy nguyên Sheet Toán 7A sang file mới rồi rút các cột không cần đi là được mà. Viết code làm gì cho nó phức tạp (đừng có nói là những việc này các thày cũng không biết nốt nha).

Mình muốn làm tệp này cho nhiều người trong trường mình dùng chứ không phải cho riêng mình.

 

[TrungChinhs]

Dù làm thủ công hay VBA thì trước khi rút cột hoặc copy cũng phải chuyển toàn bộ công thức về Value (tức là không giữ được công thức cũ) ngoại trừ dùng phương pháp ẩn cột.

Code copy sheet sang file mới

Sub CopySheet()
    Application.DisplayAlerts = False
    Sheets("Toan").Copy
    With ActiveSheet
        .UsedRange.Value = .UsedRange.Value
        .DrawingObjects.Delete
    End With
End Sub

Code Tạo file HK1 (copy Sheet sang file mới và rút các cột không cần thiết)

Sub HK_1()
    Application.ScreenUpdating = False
    Call CopySheet
    Union([c:p], [r:ai]).EntireColumn.Delete
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Replace(ThisWorkbook.Name, ".xls", "") & "HK1"
End Sub

Code Tạo file HK2 (copy Sheet sang file mới và rút các cột không cần thiết)

Sub HK_2()
    Application.ScreenUpdating = False
    Call CopySheet
    Union([a:q], [t:ag]).EntireColumn.Delete
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Replace(ThisWorkbook.Name, ".xls", "") & "HK2"
End Sub

 

[titanic20072007]

Dù làm thủ công hay VBA thì trước khi rút cột hoặc copy cũng phải chuyển toàn bộ công thức về Value (tức là không giữ được công thức cũ) ngoại trừ dùng phương pháp ẩn cột.

Code copy sheet sang file mới

Sub CopySheet()
    Application.DisplayAlerts = False
    Sheets("Toan").Copy
    With ActiveSheet
        .UsedRange.Value = .UsedRange.Value
        .DrawingObjects.Delete
    End With
End Sub

Code Tạo file HK1 (copy Sheet sang file mới và rút các cột không cần thiết)

Sub HK_1()
    Application.ScreenUpdating = False
    Call CopySheet
    Union([c:p], [r:ai]).EntireColumn.Delete
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Replace(ThisWorkbook.Name, ".xls", "") & "HK1"
End Sub

Code Tạo file HK2 (copy Sheet sang file mới và rút các cột không cần thiết)

Sub HK_2()
    Application.ScreenUpdating = False
    Call CopySheet
    Union([a:q], [t:ag]).EntireColumn.Delete
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Replace(ThisWorkbook.Name, ".xls", "") & "HK2"
End Sub

Cảm ơn bạn đã giúp. Cách làm của bạn rất gọn và hay. Xin lỗi vì lại làm phiền bạn thêm một chút nữa nhé. Nếu mình muốn cho GV họ nhập tên môn cần tính điểm vào ô A1 của sheet2 khi đó tên của tệp mới được lấy từ ô A1 đó thì làm thế nào? Vì mình muốn một tệp này có thể tính cho nhiều môn mà.

 

[Quang_Hải]

Mạn phép mượn code của bạn TrungChinh

Bạn có thế nhập tên lớp và tên môn vào sheet2 để tạo file nhưng không nên có dấu tiếng Việt vì có khả năng báo lỗi.

 

[titanic20072007]

cảm ơn các bạn đã giúp. Chúc một năm mới Vui vẻ, Hạnh phúc, ....

 

[titanic20072007]

Có một vấn đề mới nảy sinh đó là mình muốn khóa nhứng ô tính chứa công thức để mọi người khi sử dụng không vô tính xóa mất. Nhưng khi khóa thì code trên không chạy được nữa, chính xác là chỉ copy được toàn bộ sheet còn chưa xóa được các cột không cần và báo lỗi Run time 1004 dừng ở dòng .UsedRange.Value = .UsedRange.Value. Vậy có cách nào xóa được cả các cột, các ô đã bị khóa không? Và có cách nào để khóa tất cả các ô ở file mới không vì tệp này được đưa cho gv chủ nhiệm lớp và nhà trường nên mình muốn tệp mới này không ai có thể sửa dữ liệu được ngoài gv đã tạo ra nó. Mong các bạn chỉnh giúp.

 

[TrungChinhs]

...Vậy có cách nào xóa được cả các cột, các ô đã bị khóa không? Và có cách nào để khóa tất cả các ô ở file mới không vì tệp này được đưa cho gv chủ nhiệm lớp và nhà trường nên mình muốn tệp mới này không ai có thể sửa dữ liệu được ngoài gv đã tạo ra nó. Mong các bạn chỉnh giúp.

1. Khi dùng VBA, muốn làm việc với Sheet đã bị khóa thì trước tiên người ta dùng lệnh Unprotect sau khi làm việc xong thì dùng lệnh Protect để khóa lại.

2. Khi làm việc thủ công, nếu gặp sheet bị khóa thì Copy toàn bộ sheet và pate sang sheet mới hoặc file mới rồi sài (sửa, xóa, thêm. bớt...) thoải mái vì vậy khóa sheet cũng bằng không. Nếu chỉnh sửa code trên chẳng qua là để thể hiện chút kiến thức mà thôi chứ không thể ngăn cản được việc người ta sửa chữa đâu bạn ạ.

 

[Quang_Hải]

Bạn mở code ra và thêm những dòng màu đỏ vào. Lưu ý pass là 12345, nếu cần thì thay đổi pass tùy ý. Chịu khó ghé diễn đàn thường xuyên thì mau tiến bộ lắm.

Sub CopySheet()
Application.DisplayAlerts = False
Sheets("Toan").Unprotect Password:=12345
Sheets("Toan").Copy
With ActiveSheet
.UsedRange.Value = .UsedRange.Value
.DrawingObjects.Delete
End With
End Sub
Sub HK_1()
Application.ScreenUpdating = False
Fname = Sheets("sheet2").[b2] & Sheets("sheet2").[b1]
Call CopySheet
Union([c], [r:ai]).EntireColumn.Delete
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Fname & "HK1.xls"
Sheets("Toan").Protect Password:=12345
End Sub
Sub HK_2()
Application.ScreenUpdating = False
Fname = Sheets("sheet2").[b2] & Sheets("sheet2").[b1]
Call CopySheet
Union([a:q], [t:ag]).EntireColumn.Delete
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Fname & "HK2.xls"
Sheets("Toan").Protect Password:=12345
End Sub

 

[titanic20072007]

Cảm ơn các bạn đã giúp. Mình chỉ muốn để các cô giáo già không vô tình làm mất công thức thôi. Nếu mất công thức lại khổ mình để chỉnh lại. Còn cách bảo vệ như vậy mình biết là chẳng có tác dụng gì với những người biết.

Tiện đây hỏi các bạn:

- Mình muốn khóa tất cả các ô ở file mới tức là cả vùng A1:G59 thì làm thế nào. Mình làm như các bạn bảo là được rồi nhưng khi đó chỉ những cột nào mình đặt khóa ở tệp gốc thì nó mới khóa còn những cột nào mình để cho gv nhập dữ liệu trước khi tách file thì không tự khóa được. Để khi tệp này đưa cho gv khác hoặc người khác trong trường không vô tình làm mất dữ liệu được.
- Có code nào để tự động bật chức năng Security của lệnh Macro về Low không tức là tự cho phép chạy Macro mà không cần người dùng phải chọn trên menu
- Và có code nào tự đặt lại định dạng ngày tháng năm trong máy tính về dạng dd/mm/yyyy khi chạy tệp excel không?.
Xin lỗi vì hỏi nhiều quá. Chúc mọi người năm mới hạnh phúc.

 

[Quang_Hải]

1. Theo mình biết thì không có code nào để set mức low trong security đâu, và cũng không nên xài chức năng này rất nguy hiểm cho máy tính2. Việc đinh dang ngày tháng theo định dạng này thì có thể set thủ công mặc định trên máy, cần gì đến code. Nhưng nếu muôn code thì cũng có. Nếu bạn mốn cột C có đinh dang ngày tháng như thế thì làm thế nàyColumns("C:C").NumberFormat = "dd/mm/yy"Thân

 

[titanic20072007]

Ý mình là đặt cho Windows luôn.
Vậy có lệnh nào đặt thuộc tính cho một vùng ví dụ a1:g59 có thuộc tính là locked và hidden mình muốn khóa vùng này sau khi ra file mới. Cảm ơn

 

[Quang_Hải]

1. Start > ControlPannel >Regional and Language >Cusomize >Date và chọn định dang bạn cần
2. Ban xem file điều chỉnh lại theo yêu cầu của bạn, nếu muỗn cài pass thì xem lại những bài trả lời trước

 

[titanic20072007]

2. Ban xem file điều chỉnh lại theo yêu cầu của bạn, nếu muỗn cài pass thì xem lại những bài trả lời trước

Bạn ơi vẫn chưa được mình up file mẫu lên bạn xem chỉnh giúp mình. File sau khi tách được của mình gồm có các cột có nền vàng là các cột mình đã đặt thuộc tính khóa (từ tệp gốc) và khóa được bởi lệnh của bạn hướng dẫn ở trên, bây giờ mình muốn khóa cả các cột có nền trắng nữa thì làm thế nào (các cột trắng này ở tệp gốc để cho GV nhập điểm mình muốn khóa khi đã chuyển sang tệp mới)? Bạn chỉnh lại giúp nhé. Cảm ơn bạn.

 

[Quang_Hải]

Trong file gốc có thấy cột màu trắng đâu??? Bạn kiểm tra lại đi

 

[titanic20072007]

Xin lỗi mình quên không gửi file gốc. File gốc của mình giờ mình thêm 4 cột nữa để gv bộ môn nhập điểm các kỳ thi định kỳ dùng cho gv chủ nhiệm in thông báo cho phụ huynh cuối mỗi HK. Các cột này ở tệp gốc mình chưa đặt thuộc tính khóa vì để gv họ nhập điểm vào, nhưng mình muốn sau khi xuất ra file mới thì ở file mới khóa các cột đó lại để gv khác không vô tình làm thay đổi dữ liệu