Ich möchte per VBA-Code herausfinden ob eine Tabellenblatt mit einem bestimmten Namen (z.B. "XXX") existiert. Wenn nein, dann soll es erstellt werden, wenn ja dann soll es ohne Nachfrage gelöscht werden.
Könnte mir jemand dabei helfen?
Danke
Karsten
Sub test()
Dim ws As Worksheet
Dim srstr As String
Dim a As Boolean
a = False
srstr = InputBox("Tabellennamen")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = srstr Then a = True
Next
If a Then
Sheets(srstr).Delete
Else
Sheets.Add after:=ThisWorkbook.Sheets.Count
Sheets(ThisWorkbook.Sheets.Count).Name = srstr
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Function SheetExists(sName As String) As Boolean
Dim sh
For Each sh In Sheets
If sh.Name = sName Then
SheetExists = True
Exit Function
End If
Next
End Function
Sub ttx()
Const sName As String = "xxx"
If SheetExists(sName) Then
Application.DisplayAlerts = False
Sheets(sName).Delete
Application.DisplayAlerts = True
Else
Worksheets.Add
ActiveSheet.Name = sName
End If
End Sub
Function SheetExists(sName As String) As Boolean
Dim sh
For Each sh In Sheets
If LCase(sh.Name) = LCase(sName) Then
SheetExists = True
Exit Function
End If
Next
End Function
Sub TabLoeschenErstellen()
Const strshName = "Tabelle2" 'Der Name des zu überprüfenden Blattes
Dim shTmp As Object
On Error Resume Next 'Bei Fehler : Fortfahren im Code
Set shTmp = Sheets(strshName)
If shTmp Is Nothing Then
Worksheets.Add.Name = strshName
Else
Application.DisplayAlerts = False 'Nicht nachfragen
shTmp.Delete
Application.DisplayAlerts = True 'Nicht nachfragen
End If
Set shTmp = Nothing
End Sub
Gruß, NoNet