我正在尝试创建一个宏,可以删除活动工作表而不显示提示。以下代码可以实现这一功能...但是到了最后一张工作表就无法正常工作,无论如何都会出现提示。我不想删除最后一张工作表,同时也不希望出现"1004"错误信息。是否有办法修改以上代码,在不删除最后一张工作表的情况下,同时也不显示错误提示信息?
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
ActiveSheet
,并且只有它一个工作表的话,这个方法是可行的,但前提是工作簿中只有1个工作表。Sub DeleteActiveSheet()
If ThisWorkbook.Worksheets.Count = 1 Then
Exit Sub
Else
Application.DisplayAlerts = False
ThisWorkbook.ActiveSheet.Delete
Application.DisplayAlerts = True
End If
End Sub
如果想要删除除最后一个外的所有工作表,请按照以下顺序进行操作:
wksToStay
,类型为Worksheet
,并将其设置为最后一个工作表;Workbook.Worksheets
集合中的所有Worksheets
;wksToDelete
是否与wksToStay
具有相同的名称;Sub DeleteAllButLast()
Dim wksToStay As Worksheet
Dim wksToDelete As Worksheet
Dim i As Long
Set wksToStay = ThisWorkbook.Worksheets(Worksheets.Count)
For i = Worksheets.Count To 1 Step -1
Set wksToDelete = ThisWorkbook.Worksheets(i)
If wksToDelete.Name <> wksToStay.Name Then
Application.DisplayAlerts = False
wksToDelete.Delete
Application.DisplayAlerts = True
End If
Next
End Sub
请测试以下代码:
Sub deleteExceptTheLastSh()
If ActiveWorkbook.Sheets.count > 1 Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Else
MsgBox "This is the last sheet and it cannot be deleted!"
End If
End Sub
Sub deleteActiveSheet()
Application.DisplayAlerts = False
On Error GoTo Error
ActiveSheet.Delete
Application.DisplayAlerts = True
Exit Sub
Error:
MsgBox "you cannot delete the last worksheet in the workbook!"
End Sub
如果我理解正确的话,你不想删除最后一个工作表,并且想要避免错误提示信息。
你可以尝试这样做:
Sub deleteallbutlast()
Application.DisplayAlerts = False
If Worksheets.Count > 1 Then
ActiveSheet.Delete
Else
End
End If
Application.DisplayAlerts = True
End Sub