Dies ist die ultimative Anleitung zum Arbeiten mit Blättern / Arbeitsblättern in Excel.
Am Ende dieses Handbuchs haben wir einen Spickzettel mit gebräuchlichen Befehlen zum Arbeiten mit Blättern erstellt.
Blätter vs. Arbeitsblätter
Es gibt zwei Möglichkeiten, mit VBA auf Blätter zu verweisen. Die erste ist mit dem Sheets-Objekt:
| 1 | Sheets("Sheet1").Aktivieren |
Das andere ist mit dem Worksheets-Objekt:
| 1 | Worksheets("Blatt1").Aktivieren |
In 99% der Fälle sind diese beiden Objekte identisch. Wenn Sie online nach VBA-Codebeispielen gesucht haben, haben Sie wahrscheinlich beide verwendeten Objekte gesehen. Hier ist der Unterschied:
Die Sheets Collection enthält Arbeitsblätter UND Diagrammblätter.
Verwenden Sie also Tabellen, wenn Sie normale Arbeitsblätter UND Diagrammblätter einschließen möchten. Verwenden Sie Arbeitsblätter, wenn Sie Diagrammblätter ausschließen möchten. Für den Rest dieses Handbuchs werden wir Blätter und Arbeitsblätter austauschbar verwenden.
Referenzblätter
Es gibt verschiedene Möglichkeiten, auf Blätter zu verweisen:
- ActiveSheet
- Name der Blattregisterkarte
- Blattindexnummer
- Blattcodename
ActiveSheet
Das ActiveSheet ist das aktuell aktive Blatt. Mit anderen Worten, wenn Sie Ihren Code angehalten und Excel angesehen haben, ist es das Blatt, das sichtbar ist. Im folgenden Codebeispiel wird eine Messagebox mit dem ActiveSheet-Namen angezeigt.
| 1 | MsgBox ActiveSheet.Name |
Blattname
Sie sind wahrscheinlich am vertrautesten mit dem Verweisen auf Blätter anhand ihres Registerkartennamens:
| 1 | Sheets("TabName").Aktivieren |
Blattindexnummer
Die Blattindexnummer ist die Blattposition in der Arbeitsmappe. 1 ist das erste Blatt. 2 ist das zweite Blatt usw.:
| 1 | Blätter(1).Aktivieren |
Blattindexnummer - Letztes Blatt in der Arbeitsmappe
Um auf das letzte Blatt in der Arbeitsmappe zu verweisen, verwenden Sie Sheets.Count, um die letzte Indexnummer abzurufen:
| 1 | Sheets(Sheets.Count).Aktivieren |
Blatt „Codename“
Der Blattcodename ist der Objektname in VBA:
| 1 | Codename.Aktivieren |
Verweise auf Blätter in anderen Arbeitsmappen
Es ist auch einfach, auf Blätter in anderen Arbeitsmappen zu verweisen. Dazu müssen Sie das Workbooks-Objekt verwenden:
| 1 | Workbooks("VBA_Examples.xlsm").Worksheets("Sheet1").Activate |
Wichtig: Die Arbeitsmappe muss geöffnet sein, bevor Sie auf ihre Blätter verweisen können.
Aktivieren vs. Blatt auswählen
In einem anderen Artikel besprechen wir alles zum Aktivieren und Auswählen von Blättern. Die Kurzversion ist diese:
Wenn Sie ein Blatt aktivieren, wird es zum ActiveSheet. Dies ist das Blatt, das Sie sehen würden, wenn Sie sich Ihr Excel-Programm ansehen. Es kann jeweils nur ein Blatt aktiviert werden.
Aktivieren Sie ein Blatt
| 1 | Sheets("Sheet1").Aktivieren |
Wenn Sie ein Blatt auswählen, wird es auch zum ActiveSheet. Sie können jedoch mehrere Blätter gleichzeitig auswählen. Wenn mehrere Blätter gleichzeitig ausgewählt werden, ist das „oberste“ Blatt das ActiveSheet. Sie können das ActiveSheet jedoch innerhalb ausgewählter Blätter umschalten.
Wählen Sie ein Blatt aus
| 1 | Sheets("Sheet1").Select |
Wählen Sie mehrere Blätter aus
Verwenden Sie ein Array, um mehrere Blätter gleichzeitig auszuwählen:
| 1 | Arbeitsblätter(Array("Blatt2", "Blatt3")).Wählen |
Arbeitsblattvariable
Wenn Sie einer Variablen ein Arbeitsblatt zuweisen, können Sie das Arbeitsblatt anhand seines Variablennamens referenzieren. Dies kann Ihnen viel Tipparbeit ersparen und Ihren Code leichter lesbar machen. Es gibt auch viele andere Gründe, warum Sie Variablen verwenden möchten.
So deklarieren Sie eine Arbeitsblattvariable:
| 1 | Dim ws als Arbeitsblatt |
Weisen Sie einer Variablen ein Arbeitsblatt zu:
| 1 | Set ws = Sheets("Sheet1") |
Jetzt können Sie in Ihrem Code auf die Arbeitsblattvariable verweisen:
| 1 | ws.Aktivieren |
Durchlaufen Sie alle Blätter in der Arbeitsmappe
Arbeitsblattvariablen sind unerlässlich, wenn Sie alle Arbeitsblätter in einer Arbeitsmappe durchlaufen möchten. Der einfachste Weg, dies zu tun, ist:
| 12345 | Dim ws als ArbeitsblattFür jedes ws in ArbeitsblätternMsgBox ws.nameNächstes ws |
Dieser Code durchläuft alle Arbeitsblätter in der Arbeitsmappe und zeigt jeden Arbeitsblattnamen in einem Meldungsfeld an. Das Durchlaufen aller Blätter in einer Arbeitsmappe ist sehr nützlich, wenn Sie mehrere Arbeitsblätter gleichzeitig sperren / entsperren oder aus- / einblenden.
Arbeitsblattschutz
Arbeitsmappenschutz
Der Arbeitsmappenschutz sperrt die Arbeitsmappe vor strukturellen Änderungen wie dem Hinzufügen, Löschen, Verschieben oder Ausblenden von Arbeitsblättern.
Sie können den Arbeitsmappenschutz mit VBA aktivieren:
| 1 | ActiveWorkbook.Protect-Passwort:="Passwort" |
oder deaktivieren Sie den Arbeitsmappenschutz:
| 1 | ActiveWorkbook.UnProtect-Passwort:="Passwort" |
Hinweis: Sie können den Schutz auch ohne Passwort schützen/aufheben, indem Sie das Passwort-Argument weglassen:
| 1 | ActiveWorkbook.Protect |
Arbeitsblattschutz
Der Schutz auf Arbeitsblattebene verhindert Änderungen an einzelnen Arbeitsblättern.
Arbeitsblatt schützen
| 1 | Worksheets("Sheet1").Schützen Sie "Passwort" |
Arbeitsblattschutz aufheben
| 1 | Worksheets("Sheet1").Schutz für "Passwort" aufheben |
Es gibt eine Vielzahl von Optionen zum Schutz von Arbeitsblättern (Formatierungsänderungen zulassen, Benutzer das Einfügen von Zeilen ermöglichen usw.). Wir empfehlen die Verwendung des Makrorekorders, um Ihre gewünschten Einstellungen aufzuzeichnen.
Wir besprechen den Arbeitsblattschutz hier ausführlicher.
Sichtbare Eigenschaft des Arbeitsblatts
Sie wissen vielleicht bereits, dass Arbeitsblätter ausgeblendet werden können:
Es gibt tatsächlich drei Sichtbarkeitseinstellungen für Arbeitsblätter: Sichtbar, Ausgeblendet und Sehr versteckt.Ausgeblendete Blätter können von jedem normalen Excel-Benutzer eingeblendet werden - indem Sie mit der rechten Maustaste in den Registerkartenbereich des Arbeitsblatts klicken (siehe oben). VeryHidden Sheets können nur mit VBA-Code oder im VBA-Editor eingeblendet werden. Verwenden Sie die folgenden Codebeispiele, um Arbeitsblätter auszublenden/einzublenden:
Arbeitsblatt einblenden
| 1 | Worksheets("Sheet1").Visible = xlSheetVisible |
Arbeitsblatt ausblenden
| 1 | Worksheets("Sheet1").visible = xlSheetHidden |
Arbeitsblatt sehr ausblenden
| 1 | Worksheets("Sheet1").Visible = xlSheetVeryHidden |
Ereignisse auf Arbeitsblattebene
Ereignisse sind Auslöser, die dazu führen können, dass „Ereignisprozeduren“ ausgeführt werden. Sie können beispielsweise veranlassen, dass Code jedes Mal ausgeführt wird, wenn eine Zelle in einem Arbeitsblatt geändert wird oder wenn ein Arbeitsblatt aktiviert wird.
Arbeitsblattereignisprozeduren müssen in einem Arbeitsblattmodul platziert werden:
Es gibt zahlreiche Arbeitsblatt-Ereignisse. Um eine vollständige Liste anzuzeigen, gehen Sie zu einem Arbeitsblattmodul und wählen Sie „Arbeitsblatt“ aus der ersten Dropdown-Liste. Wählen Sie dann eine Ereignisprozedur aus der zweiten Dropdown-Liste aus, um sie in das Modul einzufügen.
Arbeitsblatt-Aktivierungsereignis
Arbeitsblatt-Aktivierungsereignisse werden jedes Mal ausgeführt, wenn das Arbeitsblatt geöffnet wird.
| 123 | Privates Unterarbeitsblatt_Activate()Bereich("A1").WählenEnd Sub |
Mit diesem Code wird Zelle A1 bei jedem Öffnen des Arbeitsblatts ausgewählt (der Ansichtsbereich oben links im Arbeitsblatt zurückgesetzt).
Arbeitsblattänderungsereignis
Arbeitsblattänderungsereignisse werden immer dann ausgeführt, wenn ein Zellenwert im Arbeitsblatt geändert wird. Weitere Informationen finden Sie in unserem Tutorial zu Arbeitsblattänderungsereignissen.
Arbeitsblatt Spickzettel
Unten finden Sie einen Spickzettel mit allgemeinen Codebeispielen für die Arbeit mit Blättern in VBA
VBA-Arbeitsblätter Spickzettel
VBA-Arbeitsblätter Spickzettel| Beschreibung | Codebeispiel |
|---|---|
| Referenzieren und Aktivieren von Blättern | |
| Registerkartenname | Sheets("Eingabe").Aktivieren |
| VBA-Codename | Blatt1.Aktivieren |
| Indexposition | Blätter(1).Aktivieren |
| Blatt auswählen | |
| Blatt auswählen | Sheets("Input").Select |
| Auf Variable setzen | Dim ws als Arbeitsblatt Set ws = ActiveSheet |
| Benennen / Umbenennen | ActiveSheet.Name = "NeuerName" |
| Nächstes Blatt | ActiveSheet.Next.Activate |
| Durch alle Blätter schleifen | Dim ws als Arbeitsblatt Für jedes ws in Arbeitsblättern Msgbox ws.name Nächstes ws |
| Durchlaufen ausgewählter Blätter | Dim ws als Arbeitsblatt Für jedes ws in ActiveWindow.SelectedSheets MsgBox ws.Name Nächstes ws |
| ActiveSheet herunterladen | MsgBox ActiveSheet.Name |
| Blatt hinzufügen | Tabellen.Hinzufügen |
| Blatt und Name hinzufügen | Sheets.Add.Name = "NewSheet" |
| Blatt mit Namen aus Zelle hinzufügen | Sheets.Add.Name = range("a3").value |
| Blatt nach dem anderen hinzufügen | Sheets.Add After:=Sheets("Input") |
| Blatt hinzufügen nach und Name | Sheets.Add(After:=Sheets("Input")).Name = "NewSheet" |
| Blatt vor und Name hinzufügen | Sheets.Add(Before:=Sheets("Input")).Name = "NewSheet" |
| Blatt am Ende der Arbeitsmappe hinzufügen | Sheets.Add After:=Sheets(Sheets.Count) |
| Blatt zum Anfang der Arbeitsmappe hinzufügen | Sheets.Add(Before:=Sheets(1)).Name = "FirstSheet" |
| Blatt zu Variable hinzufügen | Dim ws als Arbeitsblatt Set ws = Sheets.Add |
| Arbeitsblätter kopieren | |
| Blatt an das Ende der Arbeitsmappe verschieben | Sheets("Sheet1").Move After:=Sheets(Sheets.Count) |
| Zur neuen Arbeitsmappe | Sheets("Sheet1").Copy |
| Ausgewählte Blätter in neue Arbeitsmappe | ActiveWindow.SelectedSheets.Copy |
| Vor einem anderen Blatt | Sheets("Sheet1").Copy Before:=Sheets("Sheet2") |
| Vor dem ersten Blatt | Sheets("Sheet1").Vorher kopieren:=Sheets(1) |
| Nach dem letzten Blatt | Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) |
| Kopieren und benennen | Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = "LastSheet" |
| Aus Zellenwert kopieren und benennen | Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Range("A1").Value |
| Zu einem anderen Arbeitsbuch | Sheets("Sheet1").Copy Before:=Workbooks("Example.xlsm").Sheets(1) |
| Blätter aus-/einblenden | |
| Blatt ausblenden | Sheets("Sheet1").visible = False oder Sheets("Sheet1").visible = xlSheetHidden |
| Blatt einblenden | Sheets("Sheet1").Visible = True oder Sheets("Sheet1").Visible = xlSheetVisible |
| Sehr Blatt ausblenden | Sheets("Sheet1").Visible = xlSheetVeryHidden |
| Blätter löschen oder löschen | |
| Blatt löschen | Sheets("Sheet1").Löschen |
| Blatt löschen (Fehlerbehandlung) | Bei Fehler Fortsetzen als nächstes Sheets("Sheet1").Löschen Bei Fehler GoTo 0 |
| Blatt löschen (keine Aufforderung) | Application.DisplayAlerts = False Sheets("Sheet1").Löschen Application.DisplayAlerts = True |
| Klares Blatt | Sheets("Sheet1").Cells.Clear |
| Nur Blattinhalt löschen | Sheets("Sheet1").Cells.ClearContents |
| Clear Sheet UsedSortiment | Sheets("Sheet1").UsedRange.Clear |
| Blätter schützen oder Schutz aufheben | |
| Schutz aufheben (kein Passwort) | Sheets("Sheet1").Schutz aufheben |
| Schutz aufheben (Passwort) | Sheets("Sheet1").Schutz für "Passwort" aufheben |
| Schützen (kein Passwort) | Sheets("Sheet1").Schützen |
| Schützen (Passwort) | Sheets("Sheet1").Schützen Sie "Passwort" |
| Schützen, aber VBA-Zugriff zulassen | Sheets("Sheet1").Protect UserInterfaceOnly:=True |
| Schutz aller Blätter aufheben | Dim ws als Arbeitsblatt Für jedes ws in Arbeitsblättern ws.Schutz "Passwort" aufheben Nächstes ws |
