Renier hat eine Arbeitsmappe mit zehn Arbeitsblättern mit den Namen Ressourcen 1 bis Ressourcen 10. Wenn er eines dieser Arbeitsblätter (z. B. Ressourcen 3) löscht, möchte Renier, dass die verbleibenden Arbeitsblätter automatisch umbenannt werden, um die Lücke zu schließen ,“ sozusagen. Daher würden Ressourcen 4 bis Ressourcen 10 automatisch in Ressourcen 3 bis Ressourcen 9 umbenannt.

Es gibt verschiedene Möglichkeiten, wie Sie diese Aufgabe mithilfe von Makros ausführen können. Ein Ansatz ist wahrscheinlich weniger automatisch als der, den Renier bevorzugen würde, aber er funktioniert hervorragend:

Sub ReNameSheets()

Dim J As Integer     Dim wks As Worksheet

J = 0     For Each wks In ActiveWorkbook.Worksheets         J = J + 1         wks.Name = "TempSheet " & J     Next wks

J = 0     For Each wks In ActiveWorkbook.Worksheets         J = J + 1         wks.Name = "Resources " & J     Next wks End Sub

Dieses Makro durchläuft einfach alle Arbeitsblätter und benennt sie gemäß der von Renier bevorzugten Konvention um. Das Makro kann bei Bedarf ausgeführt werden, wenn eine Umbenennung der Arbeitsblätter gewünscht wird.

Beachten Sie, dass der Code tatsächlich zwei Namensdurchläufe durch die Arbeitsblätter durchführt. Der erste besteht darin, die Namen der Arbeitsblätter auf temporäre Namen zu setzen, und der zweite besteht darin, sie auf endgültige Namen festzulegen. Die beiden Durchgänge helfen dabei, ein potenzielles Problem zu vermeiden, wenn Sie der Arbeitsmappe Arbeitsblätter hinzufügen. Wenn Sie beispielsweise kurz vor Ressourcen 4 ein neues Arbeitsblatt hinzufügen, wird bei einer Umbenennung in einem Durchgang versucht, das neue Arbeitsblatt in Ressourcen 4 umzubenennen, wodurch generiert wird Ein Fehler, da Arbeitsblatt 5 zu diesem Zeitpunkt auch als Ressourcen 4 bezeichnet wird. Durch zwei Durchgänge benennen Sie alles in etwas völlig Neues um und nehmen dann die endgültige Namenseinstellung vor.

Für einen automatischeren Ansatz sollten Sie das SheetBeforeDelete-Ereignis für das Arbeitsmappenobjekt verwenden. Dieses Ereignis wird (wie der Name schon sagt) unmittelbar vor dem Löschen eines Arbeitsblatts ausgelöst. Hier ist ein Beispiel für einen Event-Handler-Ansatz:

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)

Dim sPrefix As String     Dim iNum1 As Integer     Dim iNum2 As Integer     Dim wks As Worksheet

sPrefix = "Resources "

If Left(Sh.Name, Len(sPrefix)) = sPrefix Then         iNum1 = CInt(Right(Sh.Name, Len(Sh.Name) - Len(sPrefix)))

Sh.Name = "TempSheet 9999"

For Each wks In Sheets             If Left(wks.Name, Len(sPrefix)) = sPrefix Then                 iNum2 = CInt(Right(wks.Name, Len(wks.Name) - Len(Prefix)))

If iNum2 > iNum1 Then                     wks.Name = sPrefix & (iNum2 - 1)

End If             End If         Next     End If End Sub

Der Ereignishandler prüft, ob das zu löschende Arbeitsblatt mit dem angegebenen Präfix („Ressourcen“) beginnt. Wenn dies der Fall ist, wird das zu löschende Arbeitsblatt in einen temporären Namen umbenannt („TempSheet 9999“).

Anschließend durchläuft es jedes Arbeitsblatt in der Arbeitsmappe und benennt jedes Arbeitsblatt um, das das richtige Präfix und eine höhere Suffixnummer als das zu löschende Arbeitsblatt hat.

Bei diesem Ansatz gibt es zwei große Einschränkungen. Erstens funktioniert es nicht, wenn Sie Arbeitsblätter hinzufügen. Zweitens wird nur das Löschen einzelner Arbeitsblätter zuverlässig behandelt. Wenn Sie mehrere Arbeitsblätter gleichzeitig löschen, ist die Nummerierung falsch und das Makro kann aufgrund der Art und Weise, wie Excel die Löschvorgänge behandelt, abstürzen.

Wenn Sie häufig mehrere Arbeitsblätter löschen möchten, können Sie das Problem mithilfe eines anderen Ansatzes umgehen. Dieser basiert auf der Verwendung der Ereignisse SheetActivate und SheetDeactivate des Workbook-Objekts.

' These variables are declared OUTSIDE of the events, ' so they are available globally.



Dim shName As String     Dim Avail As Variant

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

' When the worksheet is deactivated, the name of     ' that worksheet is stored in the shName variable.



shName = Sh.Name End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim wks As Worksheet     Dim J As Integer

On Error Resume Next     ' The following line will generate an error if the     ' the worksheet shName was deleted. If the error     ' occurrs, then we can trigger the renaming.

Avail = Sheets(shName).Range("A1")

If Err Then         J = 0         For Each wks In ActiveWorkbook.Worksheets             J = J + 1             wks.Name = "Resources " & J         Next wks     End If     On Error GoTo 0 End Sub

Diese Ereignishandler funktionieren, weil das SheetDeactivate-Ereignis automatisch ausgelöst wird, wenn ein Arbeitsblatt übrig bleibt (z. B. wenn Sie ein anderes Arbeitsblatt aktivieren) oder wenn eines gelöscht wird. Das SheetActivate-Ereignis wird dann ausgelöst und prüft im Wesentlichen, ob das zuvor deaktivierte Blatt erreicht werden kann oder nicht. Wenn es nicht erreicht werden kann, wissen wir, dass es gelöscht wurde, und können eine Umbenennung aller Arbeitsblätter auslösen.

Der Ansatz, den Sie beim Umbenennen verwenden, liegt ganz bei Ihnen, basierend darauf, wie Sie mit Ihren Arbeitsblättern arbeiten möchten.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13572) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.