Werte aus anderen Sheets einer Range zuweisen
Schritt-für-Schritt-Anleitung
Um Werte aus verschiedenen Sheets in einen bestimmten Bereich einer Excel-Tabelle zu übertragen, kannst Du folgendes VBA-Skript verwenden. Dieses Beispiel zeigt, wie Du mit For Each Cell In Range durch die Zellen in mehreren Sheets iterierst und die Werte in einen bestimmten Bereich einfügst.
- Öffne Deine Excel-Datei und drücke
ALT + F11, um den VBA-Editor zu öffnen.
- Füge ein neues Modul hinzu (Rechtsklick auf "VBAProject" → Einfügen → Modul).
- Kopiere den folgenden Code und füge ihn in das Modul ein:
Option Explicit
Private Sub Worksheet_Activate()
Dim rngListe As Range, rngZiel As Range
Dim strName As String
strName = "ODMList" ' Bereichsname, der die Werte aufnehmen soll
Set rngListe = Nothing
Application.Range(strName).ClearContents ' Vorhandene Werte löschen
Set rngZiel = Application.Range(strName).Range("A1") ' 1. Zelle der Liste setzen
Call Bereich_Auslesen(rngBereich:=Worksheets("Philips (A)").Range("SupplierAs"))
Call Bereich_Auslesen(rngBereich:=Worksheets("Philips (EU)").Range("SupplierEU"))
Call Bereich_Auslesen(rngBereich:=Worksheets("Philips (US)").Range("SupplierUS"))
With rngListe
.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo
Application.Names(strName).RefersTo = "='" & .Parent.Name & "'!" & .Address
End With
End Sub
Private Sub Bereich_Auslesen(rngBereich As Range)
Dim rngGefunden As Range
Dim Zelle As Range
For Each Zelle In rngBereich
If Zelle <> "" Then
If rngListe Is Nothing Then
rngZiel = Zelle.Value
Set rngListe = rngZiel
Else
With rngListe
Set rngGefunden = .Find(What:=Zelle.Value, LookIn:=xlValues, lookat:=xlWhole)
If rngGefunden Is Nothing Then
Set rngZiel = rngZiel.Offset(1, 0)
rngZiel.Value = Zelle.Value
Set rngListe = Union(rngListe, rngZiel)
End If
End With
End If
End If
Next
End Sub
- Schließe den VBA-Editor und kehre zu Excel zurück.
- Aktiviere das Arbeitsblatt, um den Code auszuführen.
Häufige Fehler und Lösungen
-
Fehler: "AddItem" funktioniert nicht
Wenn Du versuchst, AddItem in einem benannten Bereich zu verwenden, wird dies nicht funktionieren. Stattdessen kannst Du die Find-Methode verwenden, um zu überprüfen, ob ein Zellinhalt bereits vorhanden ist.
-
Fehler: Werte werden nicht aktualisiert
Stelle sicher, dass Du Application.Range(strName).ClearContents verwendest, um alte Werte zu löschen, bevor neue Werte hinzugefügt werden.
Alternative Methoden
Eine Möglichkeit, die Suche und das Einfügen von Werten zu optimieren, ist die Verwendung von Arrays oder Collections, um die Werte zwischenzuspeichern, bevor sie in das Zielblatt geschrieben werden. Dies kann die Performance verbessern, besonders bei großen Datenmengen.
Dim tempArray As Collection
Set tempArray = New Collection
' Füge Werte zur Collection hinzu
tempArray.Add Zelle.Value
' Schreibe die Collection später in den Zielbereich
Praktische Beispiele
Hier ist ein einfaches Beispiel für das Abrufen von Werten aus verschiedenen Sheets. Du kannst den Code anpassen, um Werte in andere Zellen oder Bereiche zu übertragen.
For Each Cell In Worksheets("Philips (A)").Range("SupplierAs")
If Cell.Value = "MeinSupplier" Then
Range("R71").Value = Cell.Offset(0, -1).Value ' Wert rechts von "MeinSupplier"
End If
Next
Tipps für Profis
- Nutze
Option Explicit, um sicherzustellen, dass alle Variablen deklariert sind. Dies hilft, Fehler zu vermeiden.
- Verwende die
With-Anweisung, um den Code übersichtlicher zu gestalten und die Performance zu verbessern.
- Experimentiere mit der
Find-Methode, um schneller nach Werten in Deinem Sheets Range zu suchen.
FAQ: Häufige Fragen
1. Warum wird mein Bereich nicht aktualisiert?
Stelle sicher, dass Du den richtigen Bereichsnamen verwendest und dass der Code in der richtigen Subroutine ausgeführt wird.
2. Wie kann ich mehrere Werte gleichzeitig abfragen?
Du kannst eine Schleife verwenden, um durch die verschiedenen Zellen zu iterieren und die gewünschten Werte zu speichern.
3. Was ist der Unterschied zwischen Find und einer Schleife?
Find ist in der Regel schneller, da es Excel direkt anweist, die Zelle zu suchen, anstatt durch jede Zelle in einer Schleife zu iterieren.
4. Wie kann ich sicherstellen, dass keine Duplikate vorhanden sind?
Nutze die Find-Methode, um zu überprüfen, ob der Wert bereits in der Liste vorhanden ist, bevor Du ihn hinzufügst.