删除某些工作表之后的工作表 [英] Delete sheets after certain sheet

问题描述

我需要删除名为模板1"的工作表之后的所有工作表.

I need to delete all sheets after a sheet called "template 1".

到目前为止，我已经尝试过

So far I have tried

Sub SheetKiller()
    Dim i As Long
    Dim j As Long
    j = 0
    For i = 1 To Sheets.Count
        If Sheets(i).Name = "template 1" Then
            j = i
        End If
    Next i

    If j = 0 Or j = Sheets.Count Then Exit Sub

    Application.DisplayAlerts = False
        For i = Sheets.Count To j + 1 Step -1
            Sheets(i).Delete
        Next i
    Application.DisplayAlerts = True 
End Sub

但是，我收到错误消息"Worksheet类的删除方法失败".有人对此有解决方案吗?

However, I get the error message "Delete method of Worksheet class failed". Anyone got a solution to this?

推荐答案

我能想到的唯一会引发此错误的事情是在工作簿中有一个非常隐藏"的工作表. 非常隐藏"的工作表不会使用您的功能删除.试试这个:

The only thing that I can think of that would throw this error is having a "Very Hidden" sheet in your workbook. "Very Hidden" sheets will not delete using your function. Try this:

Sub SheetKiller()
    Dim i As Long
    Dim j As Long
    Dim bFound As Boolean

    j = 0
    bFound = False

    For i = 1 To Sheets.Count
        If bFound Then Sheets(i).Visible = 0
        If Sheets(i).Name = "template 1" Then
            j = i
            bFound = True
        End If
    Next i

    If j = 0 Or j = Sheets.Count Then Exit Sub

    Application.DisplayAlerts = False
        For i = Sheets.Count To j + 1 Step -1
            Sheets(i).Delete
        Next i
    Application.DisplayAlerts = True
End Sub

这会将所有要删除的工作表都设置为隐藏"，从而可以在不显示它们的情况下将其删除.如果需要保留非常隐藏"工作表，请使用以下方法:

This will set all sheets that are going to be deleted to be "Hidden", allowing them to be deleted without ever displaying them. If the "Very Hidden" sheet needs to remain, then use this:

Sub SheetKiller()
    Dim i As Long
    Dim j As Long

    j = 0
    For i = 1 To Sheets.Count
        If Sheets(i).Name = "template 1" Then
            j = i
        End If
    Next i

    If j = 0 Or j = Sheets.Count Then Exit Sub

    Application.DisplayAlerts = False
        For i = Sheets.Count To j + 1 Step -1
            If Sheets(i).Visible < 2 Then Sheets(i).Delete
        Next i
    Application.DisplayAlerts = True
End Sub

这篇关于删除某些工作表之后的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！