Início / computer / Perguntas / 1856245
Accepted
CorlissExcelling
Asked: 2024-09-19 20:19:52 +0800 CST

Como melhorar o código atual para descobrir se existem planilhas nomeadas específicas e excluí-las?

  • 772

Tenho feito algumas pesquisas e reunido alguns códigos de duas fontes e queria saber se havia espaço para melhorar o código que montei. Eu tinha um código antigo que era um pouco desajeitado.

fontes: https://excelchamps.com/vba/delete-sheet/#:~:text=Each%20sheet%20has%20a%20name%2C%20and%20you%20can,code%20would%20be%3A%20Sub%20vba_delete_sheet%28%29%20Sheets%28%22Data%22%29.Delete%20End%20Sub

https://www.automateexcel.com/vba/delete-clear-worksheet/#:~:text=With%20On%20Error%20Resume%20Next%20you%20can%20tell,if%20so%20delete%20it.%3A %20If%20RangeExists%28%22Sheet1%22%29%20Then%20Sheets%28%22Sheet1%22%29.Excluir

Código:

Dim ws As Worksheet
Dim mySheet1 As Variant
Dim mySheet2 As Variant
Dim mySheet3 As Variant
Dim mySheet4 As Variant
Dim mySheet5 As Variant


mySheet1="S363"
mySheet2="non"
mySheet3="promos"
mySheet4="All Filtered"
mySheet5="Final Send"

Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
    If mySheet1 = ws.Name Then
      ws.Delete
 ElseIf  mySheet2 = ws.Name Then
      ws.Delete
ElseIf  mySheet3= ws.Name Then
      ws.Delete
ElseIf  mySheet4 = ws.Name Then
      ws.Delete
ElseIf  mySheet5 = ws.Name Then
      ws.Delete
    End If
Next ws

‘All other instructions here
Application.DisplayAlerts = True
End Sub
microsoft-excel

1 respostas

  1. Best Answer

    Você pode tentar este código:

    Sub DelShs()
    Dim sh As Variant
    Application.DisplayAlerts = False
    On Error Resume Next
    For Each sh In Array("S363", "non", "promos", "All Filtered", "Final Send")
        Worksheets(sh).Delete
    Next sh
    On Error GoTo 0
    Application.DisplayAlerts = True
End Sub

    Se tiver certeza de que todas as planilhas especificadas existem, você pode excluí-las de uma só vez:

    Worksheets(Array("S363", "non", "promos", "All Filtered", "Final Send")).Delete
    • 0

relate perguntas