Vba tạo sheet theo số lượng lựa chọn msgbox

THƯ VIỆN CODE MẪU

TRONG VBA

Ebook

Code mẫu trong VBA

Tác giả: Dương Mạnh Quân

####### Năm 2021

Chương 1: Code liên quan tßi Workbook

Khi làm việc vßi đối tượng Workbook, thưáng các công việc chính bạn cÁn làm là: - Chỉ trong phạm vi workbook: đóng, mở, tạo mßi. - Giữa workbook này vßi workbook khác: nạp dữ liệu (import), trích xu¿t dữ liệu (export) Do đó bạn chỉ cÁn nắm vững một số đoạn code mẫu là có thể làm việc một cách thoải mái vßi đối tượng workbook. Bạn hãy tham khảo các đoạn code mẫu dưßi đây và thử thực hành để xem cách các đoạn code hoạt động nhé.

1. Mở Workbook

Mục đích : Mở 1 file Excel thông qua cửa sổ chọn file (giống thao tác Open - Ctrl O)

Yêu c¿u: - Có bộ lọc File: lọc riêng chỉ file Excel và file CSV (mặc định là các file Excel). - Thông báo bằng MsgBox các trưáng hợp không chọn file, b¿m nút Cancel. - Khi b¿m nút Open thì file được chọn sẽ mở ra.

Code mẫu:

1 Sub Open_Single_File () 2 'Khai báo các biến sử dụng 3 Dim dk_Ten_tieu_de As String 'Tên tiêu đề cửa sổ Workbook Open 4 Dim dk_Loc_LoaiFile As String 'Lọc các loại file có trong cửa sổ Workbook Open 5 Dim dk_Loc_Index As Integer 'Thứ tự lọc mặc định 6 Dim kq_File_duoc_chon As String 'File được chọn 7 dk_Loc_LoaiFile = "Excel Files (.xls),.xls," & "CSV Files (.csv),*.csv," 8 dk_Loc_Index = 1 9 dk_Ten_tieu_de = "Select Your Input File of Choice" 10 'Mở cửa sổ Workbook Open 11 kq_File_duoc_chon = Application _ (FileFilter:=dk_Loc_LoaiFile, _ FilterIndex:=dk_Loc_Index, _ Title:=dk_Ten_tieu_de) 12 'Các trưáng hợp không thành công 13 'Trưáng hợp 1: Không có file được chọn 14 If kq_File_duoc_chon = "" Then 15 MsgBox ("Khong co file duoc chon") 16 Exit Sub 17 'Trưáng hợp 2. B¿m vào nút Cancel 18 ElseIf kq_File_duoc_chon = "False" Then 19 MsgBox ("Ban da bam lenh Huy thao tac") 20 Exit Sub 21 End If 22 'Trưáng hợp thành công: Mở file được chọn 23 Workbooks kq_File_duoc_chon 24 End Sub

Code mẫu:

1 Sub Luu_Workbook_FileDialogSaveAs () 2 'Mở cửa sổ FileDialog SaveAs 3 With Application(msoFileDialogSaveAs) 4 .FilterIndex = 2 5 .Show 6 If .SelectedItems > 0 Then 'Có file được chọn 7 ActiveWorkbook Filename:=(1), _ FileFormat:=xlOpenXMLWorkbookMacroEnabled 8 Else 'Các trưáng hợp khác không có file được chọn 9 MsgBox ("No File Selected") 10 End If 11 End With 12 End Sub

Chú ý: Khi muốn lưu file đích ở dạng Read-Only thì thêm đoạn code sau đây vào dưßi dòng 7: VBA .SelectedItems(1), vbReadOnly

1. Tạo mßi Workbook

a. Workbook mới chưa lưu 1 Sub Tao_Workbook_Moi () 2 Workbooks 3 End Sub

b. Workbook mới và lưu

1 Sub Tao_Workbook_Moi () 2 Dim new_wb As Workbook 3 Set new_wb = Workbooks 4 Dim TenWorkbook_Moi As String 5 TenWorkbook_Moi = ThisWorkbook & "" & _ ThisWorkbook(1).Range("A1") & _ ".xlsx" 6 new_wb TenWorkbook_Moi 7 End Sub

Chú ý: Khi lưu 1 Workbook thì cÁn chú ý 3 phÁn: - Đường dẫn (Path) : Nếu l¿y theo đưáng dẫn của file gốc thì dùng ThisWorkbook - Tên file : có thể đặt tại 1 ô trong file gốc và chỉ định tßi ô này (để viết tên tùy theo ý muốn) - Loại file : có thể đặt tại 1 ô trong file gốc và chỉ định tßi ô này (hoặc dùng loại file mặc định là .xlsx hoặc .xlsm)

Các trưáng hợp khác thì dùng cách FileDialogSaveAs để chỉ định rõ địa chỉ và tên File

22 .Size = 13 'Cỡ chữ .Color = 255 'Màu chữ 23 End With 24 End With 25 NewWB 'Kích hoạt Workbook mßi tạo 26 End Sub

Chú ý : Có thể kết hợp thêm code Lưu Workbook để lưu ngay file mßi được tạo

1. L¿y dữ liệu từ Workbook khác

Mục đích: Gom dữ liệu từ nhiều Workbook chi tiết vào 1 Workbook tổng hợp, trong đó: - Mở thư mục chọn file và duyệt tßi các file excel - Cho phép chọn nhiều file cùng lúc. Các file chi tiết có c¿u trúc giống nhau - Chỉ l¿y phÁn nội dung dữ liệu, không bao gồm định dạng - Sau khi l¿y dữ liệu thì đóng file đã chọn

Code mẫu:

1 Sub Import_data_from_multi_Workbooks () 2 'Khai báo các biến sử dụng

3 Dim ThisWB As Workbook, OpenWB As Workbook 'Biến Workbook 4 Dim i As Integer 'Biến vòng lặp 5 Dim lr_Data As Long 'Dòng cuối bảng nhận dữ liệu (lr = Last Row) 6 Dim fr_OpenWB As Long 'Dòng đÁu bảng cho dữ liệu (fr = First Row) 7 Dim lr_OpenWB As Long 'Dòng cuối bảng cho dữ liệu 8 Dim KhoangCach As Long 'Khoảng cách tính từ dòng đÁu tßi dòng cuối nơi cho 9 With Application(msoFileDialogFilePicker) 10 .AllowMultiSelect = True 'Cho phép chọn nhiều file cùng lúc (chọn 1 file là False) 11 .Show 12 For i = 1 To .SelectedItems 'Vòng lặp theo số file được chọn 13 Set ThisWB = ThisWorkbook 'Workbook ban đÁu 14 Set OpenWB = Workbooks(.SelectedItems(i)) 'Workbook được chọn 15 'Xác định giá trị các biến phạm vi Nơi cho, Nơi nhận 16 lr_Data = ThisWB("Data").Range("A" & Rows).End(xlUp).Row 17 fr_OpenWB = 8 18 lr_OpenWB = OpenWB( 1 ).Range("A" & Rows).End(xlUp).Row 19 KhoangCach = lr_OpenWB - fr_OpenWB + 1

Chương 2: Code liên quan tßi Worksheet

2. Một số điều cÁn lưu ý

1. Phân biệt Sheets với Worksheets - Giống nhau : Đều dùng được để gọi đối tượng Sheet và cách viết hoàn toàn giống nhau. - Khác nhau : Khi có thêm Chart Sheet (Sheet được tạo riêng để chỉ chứa biểu đồ - tạo bởi thao tác nh¿n phím F11) o Sheets: tính cả Chart Sheets o Worksheets: không bao gồm Chart Sheets

2. Bốn cách gọi Sheet: ActiveSheet là Sheet đang được kích hoạt Sheet Tab Name Ví dụ: Sheets("Data") là Sheet có tên Data trên thanh Sheet Tab Sheet Index Name Ví dụ: Sheets( 2 ) là Sheet thứ 2 trên thanh Sheet Tab tính từ trái sang Sheet Code Name Ví dụ: Sheet3 là Sheet có Code Name trong Properties VBA là Sheet

3. Phân biệt Sheet với Sheet - Sheet là kích hoạt 1 Sheet. Chỉ có duy nh¿t 1 Sheet được kích hoạt tại 1 thái điểm. Khi Sheet được Activate thì nó trở thành ActiveSheet - Sheet là chọn 1 Sheet. Nhưng có thể chọn nhiều Sheet cùng một lúc. o Nếu chỉ chọn 1 Sheet duy nh¿t: Sheet này cũng trở thành ActiveSheet o Nếu chọn nhiều Sheet cùng 1 lúc: chỉ Sheet đÁu tiên bên trái trong nhóm Sheet này được tính là ActiveSheet. o Code chọn nhiều Sheet cùng lúc: Worksheets(Array("Sheet2", "Sheet3")).Select (viết theo Sheet Tab Name)

2. Ân/Hiện Sheet

1. Hiện/ Àn toàn bộ các Sheet

Mục đích : Hiện/ Ân toàn bộ các Sheet có trong Workbook, không quan tâm tßi tên hay thứ tự

Code mẫu:

1 Sub MoAn_ToanBo_Sheet () 2 'Khai báo các biến sử dụng 3 Dim ws As Worksheet 4 For Each ws In ThisWorkbook 5 ws = xlSheetVisible 6 Next ws 7 End Sub

Chú ý : Các lựa chọn về Ãn/hiện sheet tại dòng 5 ws = xlSheetVisible 'Hiện, bỏ Ãn ws = xlSheetHidden 'Ân c¿p độ 1 - c¿p thông thưáng, mở Ãn trong Sheet Tab ws = xlSheetVeryHidden 'Ân c¿p độ 2 - chỉ bỏ Ãn được trong VBA

2. Thêm mßi 1 Sheet

Mục tiêu: Việc thêm mßi Sheet thưáng gắn vßi mục đích là nơi lưu trữ dữ liệu (được thêm mßi hoặc trích xu¿t từ 1 Sheet khác ra). Khi tạo Sheet mßi cÁn định vị được các yếu tố: - Tên Sheet này là gì? Không được đặt trùng tên với Sheet có sẵn. - Vị trí Sheet ở đâu trên thanh Sheet Tab? khi thêm Sheet thì Workbook sẽ luôn có nhiều hơn 1 Sheet Do đó để định vị được Sheet mßi thêm vào, chúng ta thưáng gán biến để dễ theo dõi và quản lý

Code mẫu:

1 Sub ThemMoi_Sheet () 2 Dim NewWS As Worksheet, ws As Worksheet 3 Set NewWS = ThisWorkbook.Worksheets 'Thêm mßi vào vị trí mặc định 4 Dim TenSheet As String 5 TenSheet = "Data" 'Tên Sheet muốn tạo 6 For Each ws In ThisWorkbook 'Vòng lặp xét tên các Sheet đã có 7 If ws = TenSheet Then 8 TenSheet = TenSheet & "(1)" 'Nếu trùng vßi tên đã có thì thêm ký tự (1) 9 End If 10 Next ws 11 NewWS = TenSheet 'Đặt tên cho Sheet mßi tạo 12 End Sub

Tại dòng 3: 1. Có thể viết ThisWorkbook gọn lại là Worksheets hoặc Sheets 2. Vị trí Sheet được thêm mßi: Sheets(After:=Sheets("Sheet1")) 'Thêm Sheet mßi vào sau vị trí Sheet Sheets(Before:=Sheets("Sheet1")) 'Thêm Sheet mßi vào trưßc vị trí Sheet Sheets(After:=Sheets(Sheets)) 'Thêm Sheet mßi vào vị trí cuối cùng

2. Khóa/ Mở khóa Sheet

Khóa Sheet

Worksheets("Sheet1"). Protect _ Password:="nội dung mã", _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ UserInterfaceOnly:=True, _ AllowFormattingCells:=False, _ AllowFormattingColumns:=False, _ AllowFormattingRows:=False, _ AllowInsertingColumns:=False, _ AllowInsertingRows:=False, _ AllowInsertingHyperlinks:=False, _ AllowDeletingColumns:=False, _ AllowDeletingRows:=False, _ AllowSorting:=False, _ AllowFiltering:=False, _ AllowUsingPivotTables:=False

Lệnh khóa thông thưáng Mật khÃu kèm theo 2 Options đÁu về Select

không Protect vßi code VBA 3 Options về format

3 Options về Insert

2 Options về Delete

Sử dụng Data > Sort Sử dụng Data > Filter Sử dụng Pivot Table

Mở khóa Sheet

Worksheets("Sheet1"). Unprotect 'Nếu khóa Sheet theo Options nào thì mở khóa theo Options đó Chú ý: lệnh UserInterfaceOnly:=True - Không có sẵn trong code Record Macro, phải viết thủ công trong đoạn code này. - Không lưu trữ khi đóng Workbook, phải kích hoạt lại mỗi khi mở Workbook.

Sử dụng trong sự kiện Workbook_Open để chủ động kích hoạt khi mở Workbook

1 Private Sub Workbook_Open () 2 Dim ws As Worksheet 3 For Each ws In ThisWorkbook 4 ws UserInterfaceOnly:=True 5 Next ws 6 End Sub

3. Xóa dòng, cột

1 Sub Xoa_Cot_Theo_TenCot () 2 'Khai báo các biến sử dụng 3 Dim TuCot As String 4 Dim DenCot As String 5 TuCot = "B" 6 DenCot = "D" 7 'Lệnh xóa cột 8 Columns(TuCot & ":" & DenCot).Delete 9 End Sub

1 Sub Xoa_Cot_Theo_TenCot () 2 'Khai báo các biến sử dụng 3 Dim TuCot As Long 4 Dim DenCot As Long

5 TuCot = 3 6 DenCot = 5 7 'Lệnh xóa cột 8 Range(Cells(1, TuCot), Cells(1, DenCot)).EntireColumn 9 End Sub

1 Sub Xoa_Dong_Theo_SoDong () 2 'Khai báo các biến sử dụng 3 Dim TuDong As Long 4 Dim DenDong As Long

5 TuDong = 3 6 DenDong = 5 7 'Lệnh xóa cột 8 Rows(TuDong & ":" & DenDong).Delete 9 End Sub

Chú ý: Nếu chỉ xóa 1 cột hay 1 hàng duy nh¿t thì chỉ sử dụng 1 biến.