Vba delete và add sheet trong excel
Trong một file Excel có thể tạo nhiều Sheet làm việc, nhưng bạn chỉ cần dùng một hay một số Sheet và không cần các Sheet còn lại. Với một vài Sheet thì có thể dùng cách xóa thủ công nhưng số lượng vài chục hay vài trăm Sheet thì nên sử dụng câu lệnh để thực hiện công việc này sẽ tiết kiệm thời gian và công sức đáng kể. Xem bài học trước >> VBA Excel Cấp Tốc Bài 1 - Lọc Dữ Liệu Nhiều Điều Kiện Với Advanced Filter >> VBA Excel Cấp Tốc Bài 2 - In Bảng Cửu Chương >> VBA
Excel Cấp Tốc Bài 3 - In Ấn Nhanh Hàng Loạt Mở Visual BasicMở Visual Basic để code những câu lệnh thực hiện việc xóa các Sheet không Active: Developer -> Visual Basic -> chuột phải vào Sheet 1 -> Insert -> Module. Code chương trình Xóa Sheet không ActiveCấu trúc của một chương trình vẫn là Sub sau đó đến tên chương trình enter phần kết trúc chương trình sẽ tự động sinh ra.
Khi kích vào Run để chạy chương trình và sẽ hiện thông báo bạn thực sự có muốn xóa không bạn kích chọn Delete để xóa và thông báo cứ hiện lênh khi xóa mỗi Sheet. Với số lượng rất nhiều Sheet nên không thể tích Delete mãi được thì mình sử dụng dòng lệnh: Application.DisplayAlerts = False và dòng lệnh: Application.DisplayAlerts = True hiện thông báo khi thực hiện lại xóa Sheet để tránh nhưng nguy hiểm cho người sử dụng sau. Tạo Nút Nhấn ButtonPhần này tùy vào nhu cầu hay chương trình của bạn có nên tạo Button hay không. Mình sẽ hướng dẫn cho các bạn cần tạo: Developer -> Insert -> chọn hình giữ chuột và kéo -> tích vào Macro mà bạn muốn gán cho Button -> ok. Đoạn code VBA của chương trình xóa Sheets không Active:
Xem bài tiếp theo >> VBA Excel Cấp Tốc Bài 5 - Copy File Từ Một Thư Mục Sang Thư Mục Khác This VBA Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. You can get immediate free access to these example workbooks by subscribing to the Power Spreadsheets Newsletter. Use the following Table of Contents to navigate to the section you're interested in.
Related VBA and Macro TutorialsThe following VBA and macro tutorials may help you better understand and implement the contents below:
You can find additional VBA and Macro Tutorials in the Archives. #1: Delete Sheet by PositionVBA Code to Delete Sheet by PositionTo delete a sheet by its position using VBA, use a statement with the following structure: Sheets(SheetIndex).Delete Process Followed by VBA Code to Delete Sheet by PositionVBA Statement Explanation
Macro Example to Delete Sheet by PositionThe following macro deletes the first sheet (Sheets(mySheetIndex)) in the workbook where the macro is stored (ThisWorkbook). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False). Sub deleteSheet() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/ 'declare variable to hold index number of sheet you want to delete Dim mySheetIndex As Long 'specify index number of sheet you want to delete mySheetIndex = 1 'suppress the dialog box that asks the user to confirm the sheet deletion Application.DisplayAlerts = False 'identify sheet you want to delete, and delete it ThisWorkbook.Sheets(mySheetIndex).Delete 're-enable the display of alerts and messages Application.DisplayAlerts = True End Sub Effects of Executing Macro Example to Delete Sheet by PositionThe following GIF illustrates the results of executing the macro example. The first sheet in the workbook (Sheet1) is deleted. #2: Delete Active SheetVBA Code to Delete Active SheetTo delete the active sheet with VBA, use a statement with the following structure: ActiveSheet.Delete Process Followed by VBA Code to Delete Active SheetVBA Statement Explanation
Macro Example to Delete Active SheetThe following macro deletes the active sheet (ActiveSheet). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False). Sub deleteActiveSheet() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/ 'suppress the dialog box that asks the user to confirm the sheet deletion Application.DisplayAlerts = False 'identify active sheet, and delete it ActiveSheet.Delete 're-enable the display of alerts and messages Application.DisplayAlerts = True End Sub Effects of Executing Macro Example to Delete Active SheetThe following GIF illustrates the results of executing the macro example. The active sheet (Sheet2) is deleted. #3: Delete Sheet by NameVBA Code to Delete Sheet by NameTo delete a sheet by name using VBA, use a statement with the following structure: Sheets(SheetName).Delete Process Followed by VBA Code to Delete Sheet by NameVBA Statement Explanation
Macro Example to Delete Sheet by NameThe following macro deletes the sheet named “delete Sheet” (Sheets(mySheetName)) in the workbook where the macro is stored (ThisWorkbook). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False). Sub deleteSheetByName() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/ 'declare variable to hold name of sheet you want to delete Dim mySheetName As String 'specify name of sheet you want to delete mySheetName = "delete sheet" 'suppress the dialog box that asks the user to confirm the sheet deletion Application.DisplayAlerts = False 'identify sheet you want to delete, and delete it ThisWorkbook.Sheets(mySheetName).Delete 're-enable the display of alerts and messages Application.DisplayAlerts = True End Sub Effects of Executing Macro Example to Delete Sheet by NameThe following GIF illustrates the results of executing the macro example. The sheet named “delete sheet” is deleted. #4: Delete Sheet Without Prompt or WarningVBA Code to Delete Sheet Without Prompt or WarningTo delete a sheet without Excel displaying the usual prompt (warning) with VBA, use a macro with the following statement structure: Application.DisplayAlerts = False Sheets(SheetName).Delete Application.DisplayAlerts = True Process Followed by VBA Code to Delete Sheet Without Prompt or WarningVBA Statement ExplanationLines #1 and #3: Application.DisplayAlerts = False | Application.DisplayAlerts = True
Line #2: Sheets(SheetName).Delete
Macro Example to Delete Sheet Without Prompt or WarningThe following macro deletes the sheet named “delete sheet no prompt” (Sheets(mySheetName)) in the workbook where the macro is stored (ThisWorkbook) without displaying the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False). Sub deleteSheetNoPrompt() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/ 'declare variable to hold name of sheet you want to delete Dim mySheetName As String 'specify name of sheet you want to delete mySheetName = "delete sheet no prompt" 'suppress the dialog box that asks the user to confirm the sheet deletion Application.DisplayAlerts = False 'identify sheet you want to delete, and delete it ThisWorkbook.Sheets(mySheetName).Delete 're-enable the display of alerts and messages Application.DisplayAlerts = True End Sub Effects of Executing Macro Example to Delete Sheet Without Prompt or WarningThe following GIF illustrates the results of executing the macro example. The sheet named “delete sheet no prompt” is deleted without a prompt or warning. #5: Delete Sheet if it ExistsVBA Code to Delete Sheet if it ExistsTo delete a sheet if it exists with VBA, use a macro with the following statement structure: On Error Resume Next Sheets(SheetName).Delete On Error GoTo 0 Process Followed by VBA Code to Delete Sheet if it ExistsVBA Statement ExplanationLine #1: On Error Resume Next
Line #2: Sheets(SheetName).Delete
Line #3: On Error GoTo 0
Macro Example to Delete Sheet if it ExistsThe following macro deletes the sheet named “delete sheet if exists” (Sheets(mySheetName)) in the workbook where the macro is stored (ThisWorkbook), if such sheet exists. If the sheet doesn't exist, the macro handles the error. The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False). Sub deleteSheetIfExists() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/ 'declare variable to hold name of sheet you want to delete Dim mySheetName As String 'specify name of sheet you want to delete mySheetName = "delete sheet if exists" 'suppress the dialog box that asks the user to confirm the sheet deletion Application.DisplayAlerts = False 'enable error handling to deal with error that occurs when (if) you try to delete a sheet that doesn't exist On Error Resume Next 'identify sheet you want to delete, and delete it ThisWorkbook.Sheets(mySheetName).Delete 'disable error handling On Error GoTo 0 're-enable the display of alerts and messages Application.DisplayAlerts = True End Sub Effects of Executing Macro Example to Delete Sheet if it ExistsThe following GIF illustrates the results of executing the macro example. The sheet named “delete sheet if exists” is deleted. #6: Delete Multiple SheetsVBA Code to Delete Multiple SheetsTo delete multiple sheets with VBA, use a statement with the following structure: Sheets(Array(SheetsList)).Delete Process Followed by VBA Code to Delete Multiple SheetsVBA Statement Explanation
Macro Example to Delete Multiple SheetsThe following macro deletes (i) the first 2 sheets, and (ii) the sheets named “delete multiple sheets 1” and “delete multiple sheets 2” (Sheets(mySheetNames)), in the workbook where the macro is stored (ThisWorkbook). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False). Sub deleteMultipleSheets() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/ 'declare variable to hold Variant containing array whose elements are the index numbers or names of the sheets you want to delete Dim mySheetNames() As Variant 'specify (using index numbers or names) the sheets you want to delete mySheetNames = Array(1, 2, "delete multiple sheets 1", "delete multiple sheets 2") 'suppress the dialog box that asks the user to confirm the sheet deletion Application.DisplayAlerts = False 'identify sheets you want to delete, and delete them ThisWorkbook.Sheets(mySheetNames).Delete 're-enable the display of alerts and messages Application.DisplayAlerts = True End Sub Effects of Executing Macro Example to Delete Multiple SheetsThe following GIF illustrates the results of executing the macro example. The (i) the first 2 sheets (Sheet6 and Sheet7), and (ii) the sheets named “delete multiple sheets 1” and “delete multiple sheets 2”, are deleted. References to VBA Constructs Used in this VBA TutorialUse the following links to visit the appropriate webpage in the Microsoft Developer Network:
|