Chào mừng bạn đến blog Cốc Cốc News Tin Tức Trang Chủ

Table of Content

Home
Cách
HướngDẫn

Mẹo Vba delete và add sheet trong excel ?

Kinh Nghiệm Hướng dẫn Vba delete và add sheet trong excel 2022

Hà Quang Phong đang tìm kiếm từ khóa Vba delete và add sheet trong excel được Cập Nhật vào lúc : 2022-10-25 13:20:12 . Với phương châm chia sẻ Mẹo Hướng dẫn trong nội dung bài viết một cách Chi Tiết 2022. Nếu sau khi tham khảo tài liệu vẫn ko hiểu thì hoàn toàn có thể lại Comments ở cuối bài để Ad lý giải và hướng dẫn lại nha.

Trong một file Excel hoàn toàn có thể tạo nhiều Sheet thao tác, nhưng bạn chỉ việc dùng một hay một số trong những Sheet và không còn nhu yếu các Sheet còn sót lại. Với một vài Sheet thì hoàn toàn 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 việc làm này sẽ tiết kiệm thời gian và công sức của con người đáng kể.

Nội dung chính Show
    Video hướng dẫnMở Visual BasicCode chương trình Xóa Sheet không ActiveTạo Nút Nhấn ButtonRelated VBA and Macro Tutorials#1: Delete Sheet by PositionVBA Code to Delete Sheet by PositionProcess Followed by VBA Code to Delete Sheet by PositionVBA Statement ExplanationMacro Example to Delete Sheet by PositionEffects of Executing Macro Example to Delete Sheet by Position#2: Delete Active SheetVBA Code to Delete Active SheetProcess Followed by VBA Code to Delete Active SheetVBA Statement ExplanationMacro Example to Delete Active SheetEffects of Executing Macro Example to Delete Active Sheet#3: Delete Sheet by NameVBA Code to Delete Sheet by NameProcess Followed by VBA Code to Delete Sheet by NameVBA Statement ExplanationMacro Example to Delete Sheet by NameEffects of Executing Macro Example to Delete Sheet by Name#4: Delete Sheet Without Prompt or WarningVBA Code to Delete Sheet Without Prompt or WarningProcess Followed by VBA Code to Delete Sheet Without Prompt or WarningVBA Statement ExplanationMacro Example to Delete Sheet Without Prompt or WarningEffects of Executing Macro Example to Delete Sheet Without Prompt or Warning#5: Delete Sheet if it ExistsVBA Code to Delete Sheet if it ExistsProcess Followed by VBA Code to Delete Sheet if it ExistsVBA Statement ExplanationMacro Example to Delete Sheet if it ExistsEffects of Executing Macro Example to Delete Sheet if it Exists#6: Delete Multiple SheetsVBA Code to Delete Multiple SheetsProcess Followed by VBA Code to Delete Multiple SheetsVBA Statement ExplanationMacro Example to Delete Multiple SheetsEffects of Executing Macro Example to Delete Multiple SheetsReferences to VBA Constructs Used in this VBA Tutorial

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

Video hướng dẫn

[embed]https://www.youtube.com/watch?v=NGC1rCMaUFQ[/embed]

Mở Visual Basic

Mở Visual Basic để code những câu lệnh thực hiện việc xóa những Sheet không Active: Developer -> Visual Basic ->  chuột phải vào Sheet 1 -> Insert -> Module.

Vba delete và add sheet trong excel

Code chương trình Xóa Sheet không Active

Cấ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.

Vba delete và add sheet trong excel
    Thực hiện khai báo một biến mang tên là biến Worksheet với dòng lệnh:Sử dụng vòng lập For để quét tất cả những Sheet xem Sheet nào không phải là Sheet Active thì nó sẽ xóa đi, lệnh này với cú pháp là: For Each ws In ThisWorkbook.Sheets -> enter và Next để kết thúc vòng lặp.Tiếp tục If ws.Name <> ThisWorkbook.Activesheet.Name Then nghĩa là nếu quét đến cái Sheet mà khác cái Sheet mình đang lựa chọn thao tác thì sẽ xóa đi -> enter và End If để kết thúc Điều kiện If.Bên trong hàm If viết câu lệnh nếu là khác thì xóa: Ws.Delete

Vba delete và add sheet trong excel

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ó mong ước 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.

Vba delete và add sheet trong excel

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 những người dân tiêu dùng sau.

Vba delete và add sheet trong excel

Tạo Nút Nhấn Button

Phần này tùy vào nhu yếu hay chương trình của bạn có nên tạo Button hay là không. Mình sẽ hướng dẫn cho những 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.

Vba delete và add sheet trong excel

Đoạn code VBA của chương trình xóa Sheets không Active:

Sub Xoa_sheet()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
    If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
        ws.Delete
    End If
Next
Application.DisplayAlerts = True
End Sub

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

Vba delete và add sheet trong excelIn this VBA Tutorial, you learn how to delete sheets in Excel (in different ways) with macros.

This VBA Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. You can get immediate không lấy phí 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 Tutorials#1: Delete Sheet by Position
      VBA Code to Delete Sheet by PositionProcess Followed by VBA Code to Delete Sheet by PositionVBA Statement ExplanationMacro Example to Delete Sheet by PositionEffects of Executing Macro Example to Delete Sheet by Position
    #2: Delete Active Sheet
      VBA Code to Delete Active SheetProcess Followed by VBA Code to Delete Active SheetVBA Statement ExplanationMacro Example to Delete Active SheetEffects of Executing Macro Example to Delete Active Sheet
    #3: Delete Sheet by Name
      VBA Code to Delete Sheet by NameProcess Followed by VBA Code to Delete Sheet by NameVBA Statement ExplanationMacro Example to Delete Sheet by NameEffects of Executing Macro Example to Delete Sheet by Name
    #4: Delete Sheet Without Prompt or Warning
      VBA Code to Delete Sheet Without Prompt or WarningProcess Followed by VBA Code to Delete Sheet Without Prompt or WarningVBA Statement ExplanationMacro Example to Delete Sheet Without Prompt or WarningEffects of Executing Macro Example to Delete Sheet Without Prompt or Warning
    #5: Delete Sheet if it Exists
      VBA Code to Delete Sheet if it ExistsProcess Followed by VBA Code to Delete Sheet if it ExistsVBA Statement ExplanationMacro Example to Delete Sheet if it ExistsEffects of Executing Macro Example to Delete Sheet if it Exists
    #6: Delete Multiple Sheets
      VBA Code to Delete Multiple SheetsProcess Followed by VBA Code to Delete Multiple SheetsVBA Statement ExplanationMacro Example to Delete Multiple SheetsEffects of Executing Macro Example to Delete Multiple Sheets
    References to VBA Constructs Used in this VBA Tutorial

The following VBA and macro tutorials may help you better understand and implement the contents below:

    General VBA constructs and structures:
      Learn about commonly-used VBA terms here.Learn how to work with the Visual Basic Editor (VBE) here.Learn how to create and work with VBA Sub procedures here.Learn how to work with object methods here.Learn how to declare and work with variables here.Learn how to work with data types here.Learn how to work with arrays here.
    Practical VBA applications and macro examples:
      Learn other operations you can carry out when working with Excel worksheets here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Delete Sheet by Position

VBA Code to Delete Sheet by Position

To 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 Position

Vba delete và add sheet trong excel

VBA Statement Explanation

Item: Sheets.
    VBA Construct: Workbook.Sheets property.Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you giảm giá with. Identify a single object from this Sheets collection by specifying the appropriate index number (SheetIndex).

    When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you giảm giá with.

    When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you giảm giá with.

Item: SheetIndex.
    VBA Construct: Index parameter/number of the sheet you want to delete.Description: The Index parameter/number of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

    The Index parameter/number represents the position of the sheet, worksheet or chart sheet in the tab bar of the workbook you giảm giá with, from left to right. For example, 1 is the first (leftmost) sheet/worksheet/chart sheet.

    When specifying the Index parameter/number, consider the following:

      The count usually includes hidden sheets/worksheets/chart sheets as well.If you're working with the Workbook.Worksheets property, the count includes worksheets but not chart sheets.If you're working with the Workbook.Charts property, the count includes chart sheets but not worksheets.

    If you explicitly declare a variable to represent SheetIndex, use the Long data type.

Item: Delete.
    VBA Construct: Worksheet.Delete method or Chart.Delete method.Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetIndex).

    When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section below to delete a sheet with no prompt.

Macro Example to Delete Sheet by Position

The 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 Position

The following GIF illustrates the results of executing the macro example. The first sheet in the workbook (Sheet1) is deleted.

Vba delete và add sheet trong excel

#2: Delete Active Sheet

VBA Code to Delete Active Sheet

To delete the active sheet with VBA, use a statement with the following structure:

ActiveSheet.Delete

Process Followed by VBA Code to Delete Active Sheet

Vba delete và add sheet trong excel

VBA Statement Explanation

Item: ActiveSheet.
    VBA Construct: Application.ActiveSheet property.Description: The Application.ActiveSheet property returns an object representing the active sheet.
Item: Delete.
    VBA Construct: Worksheet.Delete method or Chart.Delete method.Description: The Delete method deletes the object (worksheet or chart sheet) returned by ActiveSheet (the active sheet).

    When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section below to delete a sheet with no prompt.

Macro Example to Delete Active Sheet

The 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 Sheet

The following GIF illustrates the results of executing the macro example. The active sheet (Sheet2) is deleted.

Vba delete và add sheet trong excel

#3: Delete Sheet by Name

VBA Code to Delete Sheet by Name

To 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 Name

Vba delete và add sheet trong excel

VBA Statement Explanation

Item: Sheets.
    VBA Construct: Workbook.Sheets property.Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you giảm giá with. Identify a single object from this Sheets collection by specifying the appropriate name (SheetName).

    When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you giảm giá with.

    When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you giảm giá with.

Item: SheetName.
    VBA Construct: Name of the sheet you want to delete.Description: The name of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

    For these purposes, the sheet name is that displayed in the tab of the worksheet or chart sheet. If you explicitly declare a variable to represent SheetName, use the String data type.

Item: Delete.
    VBA Construct: Worksheet.Delete method or Chart.Delete method. Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetName).

    When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section below to delete a sheet with no prompt.

Macro Example to Delete Sheet by Name

The 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 Name

The following GIF illustrates the results of executing the macro example. The sheet named “delete sheet” is deleted.

Vba delete và add sheet trong excel

#4: Delete Sheet Without Prompt or Warning

VBA Code to Delete Sheet Without Prompt or Warning

To 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 Warning

Vba delete và add sheet trong excel

VBA Statement Explanation

Lines #1 and #3: Application.DisplayAlerts = False | Application.DisplayAlerts = TrueItem: Application.DisplayAlerts.
    VBA Construct: Application.DisplayAlerts property.Description: The Application.DisplayAlerts property allows you to specify whether Excel displays alerts and messages while the macro is running. When you delete a sheet, the main alert that Excel usually displays (and you want to handle) is the dialog box that prompts the user to confirm the sheet deletion.

    Vba delete và add sheet trong excel

    The default value of the Application.DisplayAlerts property is True. In such cases, Excel displays the dialog box prompting the user to confirm the sheet deletion.

Item: False.
    VBA Construct: New property value of Application.DisplayAlerts property.Description: When you delete a sheet, you can suppress the dialog box that prompts the user to confirm the sheet deletion, by setting the Application.DisplayAlerts property to False.

    When you set Application.DisplayAlerts to False and Excel requires a response, Excel chooses the default response. When you delete a sheet, the default response to the dialog box prompting the user to confirm the sheet deletion is “Delete”. This results in Excel deleting the sheet.

Item: True.
    VBA Construct: New property value of Application.DisplayAlerts property.Description: The default value of the Application.DisplayAlerts property is True. This results in Excel displaying alerts and messages while a macro is running.

    Generally, when you set Application.DisplayAlerts to False, Excel sets the property back to True upon finishing macro execution. Since there are exceptions (such as executing cross-process code), you can explicitly set Application.DisplayAlerts back to True after deleting the sheet.

Line #2: Sheets(SheetName).DeleteItem: Sheets.
    VBA Construct: Workbook.Sheets property.Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you giảm giá with. Identify a single object from this Sheets collection by specifying the appropriate name (SheetName).

    When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you giảm giá with.

    When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you giảm giá with.

Item: SheetName.
    VBA Construct: Name of the sheet you want to delete.Description: The name of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

    For these purposes, the sheet name is that displayed in the tab of the worksheet or chart sheet. If you explicitly declare a variable to represent SheetName, use the String data type.

Item: Delete.
    VBA Construct: Worksheet.Delete method or Chart.Delete method.Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetName).

Macro Example to Delete Sheet Without Prompt or Warning

The 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 Warning

The following GIF illustrates the results of executing the macro example. The sheet named “delete sheet no prompt” is deleted without a prompt or warning.

Vba delete và add sheet trong excel

#5: Delete Sheet if it Exists

VBA Code to Delete Sheet if it Exists

To 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 Exists

Vba delete và add sheet trong excel

VBA Statement Explanation

Line #1: On Error Resume NextItem: On Error Resume Next.
    VBA Construct: On Error Resume Next statement.Description: The On Error Resume Next statement specifies that, if an error occurs, control goes to the statement immediately following the statement where the error occurs. Execution continues that statement that follows that where the error occurs.

    “Sheets(SheetName).Delete” usually returns run-time error 9 (subscript out of range) when the sheet identified by Sheets(SheetName) doesn't exist. Without the On Error Resume Next statement, such error results in Excel displaying an error message and stopping macro execution.

    Therefore:

      If the sheet named SheetName exists, Excel deletes the sheet as specified by “Sheets(SheetName).Delete”.If the sheet named SheetName doesn't exist, execution of the macro continues on the statement following “Sheet(SheetName).Delete”. Due to the On Error Resume Next statement, Excel handles the run-time error.
Line #2: Sheets(SheetName).DeleteItem: Sheets.
    VBA Construct: Workbook.Sheets property.Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you giảm giá with. Identify a single object from this Sheets collection by specifying the appropriate name (SheetName).

    When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you giảm giá with.

    When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you giảm giá with.

Item: SheetName.
    VBA Construct: Name of the sheet you want to delete.Description: The name of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

    For these purposes, the sheet name is that displayed in the tab of the worksheet or chart sheet. If you explicitly declare a variable to represent SheetName, use the String data type.

Item: Delete.
    VBA Construct: Worksheet.Delete method or Chart.Delete method.Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetName).

    When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section above to delete a sheet with no prompt.

Line #3: On Error GoTo 0Item: On Error GoTo 0.
    VBA Construct: On Error GoTo 0 statement.Description: The On Error GoTo 0 statement disables the error handling specified by the On Error Resume Next statement. If you omit the On Error GoTo 0 statement, Excel generally disables the error handler automatically when exiting the procedure.

Macro Example to Delete Sheet if it Exists

The 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 giảm giá 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 Exists

The following GIF illustrates the results of executing the macro example. The sheet named “delete sheet if exists” is deleted.

Vba delete và add sheet trong excel

#6: Delete Multiple Sheets

VBA Code to Delete Multiple Sheets

To delete multiple sheets with VBA, use a statement with the following structure:

Sheets(Array(SheetsList)).Delete

Process Followed by VBA Code to Delete Multiple Sheets

Vba delete và add sheet trong excel

VBA Statement Explanation

Item: Sheets.
    VBA Construct: Workbook.Sheets property.Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you giảm giá with. Identify a single object from this Sheets collection by specifying the appropriate name (SheetName).

    When deleting worksheets, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you giảm giá with.

    When deleting chart sheets, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you giảm giá with.

Item: Array(…).
    VBA Construct: Array function.Description: The Array function returns a Variant containing an array.
Item: SheetsList.
    VBA Construct: Argument list of Array function.Description: A comma-delimited list of the values you assign to each of the array elements.

    You can generally identify specific objects from the Sheets collection you work with using an index number or the sheet name, as follows:

      The index number represents the position of a sheet, worksheet or chart sheet in the tab bar of the workbook you giảm giá with, from left to right. For example, 1 is the first (leftmost) sheet/worksheet/chart sheet.

      When specifying the Index parameter/number, consider the following:

        The count usually includes hidden sheets/worksheets/chart sheets as well.If you're working with the Workbook.Worksheets property, the count includes worksheets but not chart sheets.If you're working with the Workbook.Charts property, the count includes chart sheets but not worksheets.
      The sheet name is that displayed in the tab of the worksheet or chart sheet.
Item: Delete.
    VBA Construct: Worksheet.Delete method or Chart.Delete method.Description: The Delete method deletes the object (worksheets or chart sheets) you identify with Sheets(Array(SheetsList)).

    When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section above to delete a sheet with no prompt.

Macro Example to Delete Multiple Sheets

The 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 Sheets

The 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.

Vba delete và add sheet trong excel

References to VBA Constructs Used in this VBA Tutorial

Use the following links to visit the appropriate webpage in the Microsoft Developer Network:

Identify workbook containing sheets, worksheets or chart sheets to delete:
    Application.ThisWorkbook property.Application.ActiveWorkbook property.Application.Workbooks property.Workbook object.
Identify sheets, worksheets and chart sheets:
    Workbook.Sheets property.Application.ActiveSheet property.Sheets object.Workbook.Worksheets property.Worksheet object.Workbook.Charts property.Chart object.
Delete sheets, worksheets and chart sheets:Worksheet.Delete method.Chart.Delete method.Prevent Excel from displaying prompts or warnings when deleting a sheet, worksheet or chart sheet:
    Application.DisplayAlerts property.
Handle errors:
    On Error statement.
Create an array containing the names of several sheets, worksheets or chart sheets to delete:
    Array function.
Work with variables and data types:
    Dim statement.= operator.Data types:
      Long data type.String data type.Variant data type.
Tải thêm tài liệu liên quan đến nội dung bài viết Vba delete và add sheet trong excel VBA delete file Delete row VBA Active sheet VBA VBA delete column

Review Vba delete và add sheet trong excel ?

Bạn vừa đọc nội dung bài viết Với Một số hướng dẫn một cách rõ ràng hơn về Video Vba delete và add sheet trong excel tiên tiến nhất

Chia Sẻ Link Download Vba delete và add sheet trong excel miễn phí

Pro đang tìm một số trong những Chia Sẻ Link Cập nhật Vba delete và add sheet trong excel miễn phí.

Thảo Luận thắc mắc về Vba delete và add sheet trong excel

Nếu sau khi đọc nội dung bài viết Vba delete và add sheet trong excel vẫn chưa hiểu thì hoàn toàn có thể lại Comments ở cuối bài để Ad lý giải và hướng dẫn lại nha #Vba #delete #và #add #sheet #trong #excel - 2022-10-25 13:20:12 Vba delete và add sheet trong excel

Post a Comment