Excel VBA: Define UsedRange: 1. Zeile weglassen

Forumthread: Excel VBA: Define UsedRange: 1. Zeile weglassen

09.03.2016 13:20:19

Kilian

Hallo zusammen,
Ich habe eine Excel mit verschiedenen Tabllen. Diese Tabellen haben alle die gleiche Spaltenstruktur mit denselben Überschriften. Die Zeilenanzahl wird vermutlich unterschiedlich sein. Tabelle 1 ist leer, Tabelle2,3,4,5 usw. enthalten die Daten, die einfach nur untereinander in Tabelle 1 kopiert werden sollen. Das habe ich mit Hilfe von Google soweit hin bekommen. Mein Problem jetzt: Das Makro kopiert mir auch die jeweiligen Überschriften aus Zeile 1 mit in Tabelle 1. Das Makro soll aber immer erst in der 2. Zeile starten.

Sub TabellenKopierenUntereinander()
Dim i As Integer With ActiveWorkbook
For i = 2 To .Worksheets.Count
Set Rng = .Worksheets(i).UsedRange
Set rng1 = Worksheets(1).Cells(Rows.Count, "B").End(xlUp)(2)
Rng.Copy Destination:=rng1
Next
End With
End Sub

Ich nehme mal an, dass ich hier was mit der UsedRange verändern muss, komm aber nicht drauf, was und wie genau.
Alternativ hätte ich noch einen anderen Code gefunden, bei dem es perfekt funktioniert nur nimmt er mir immer die letzte Spalte nicht mit. Sprich, wenn ich 7 Spalten habe, kopiert mir nur 6, wenn ich 20 habe nur 19, etc.

Sub BCopy()
Dim i As Long
Dim sName As String
'Bildschirmaktualisierung ausschalten
Application.ScreenUpdating = False
'alle Blätter kopieren und in neues einfügen
For i = 2 To Worksheets.Count
Sheets(i).Select
Sheets(i).Range(Cells(2, 1), Cells(Sheets(i).UsedRange.Rows.Count, Sheets(i).UsedRange. _
Columns.Count)).Copy
Sheets(1).Paste Destination:=Sheets(1).Cells(Sheets(1).UsedRange.Rows.Count + 1, 1)
Next i
'Zwischenablage leeren
Application.CutCopyMode = False
'Gesamtblatt anzeigen
Sheets(1).Select
'optimale Anpassung der Spalten
Columns.AutoFit
'Bildschirmaktualisierung einschalten
Application.ScreenUpdating = True
End Sub

Wäre überagend, wenn mir hier jemand weiterhelfen könnte!
Beste Grüße
Kilian

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Excel VBA: Define UsedRange: 1. Zeile weglassen

09.03.2016 13:34:51

Daniel

Hi
einfachste Lösung:


Set Rng = .Worksheets(i).UsedRange.Offset(1, 0)

das Offset verschiebet den Bereich um eine zeile nach unten.
das hierbei die erste Leerzeile unterhalb der Usedrange mit kopiert wird, dürfte in der Praxis nicht weiter stören.
wenn du genau die Usedrange ohne die erste Zeile haben willst, dann so:

set Rng = Worksheets(i).Usedrange
set Rng = Intersect(Rng, Rng.Offset(1, 0))

oder

set Rng = Worksheets(i).UsedRange
set Rng = Rng.Resize(Rng.Rows.count - 1).Offset(1, 0))

oder

set Rng = Range(Worksheets(i).Range("A2"), Worksheets(i).Cells.SpecialCells(xlcelltypelastcell))

Gruß Daniel

AW: Excel VBA: Define UsedRange: 1. Zeile weglassen

09.03.2016 13:47:28

Kilian

SUUUUPER!!!
Vielen, vielen, vielen Dank Daniel!!

AW: Excel VBA: Define UsedRange: 1. Zeile weglassen

09.03.2016 15:09:35

Kilian

Hallo Daniel,
in der Ausführung habe ich nun leider doch noch ein Problem:
In der Testdatei mit 4 Zeilen funktioniert alles wunderbar.
In der "echten" Datei mit über 2000 Zeilen leider nicht.
Hier kopiert er mir den Inhalt von Tabelle 2&3 (weitere Tabellen sind zZ noch nicht mit Daten befüllt) wunderbar in Tabelle 1 und anschließend kopiert er mir nochmals Daten, von denen ich nicht weiß wo sie hergezogen werden, inkl. den Überschriften von Tabelle 1 und hängt das ganze nach unten dran.
Kann das daran liegen, dass die "Praxis"-Datei im Gegensatz zu Testdatei mit anderen Excel-Dateien verknüpft ist?
Gruß

AW: Excel VBA: Define UsedRange: 1. Zeile weglassen

09.03.2016 15:20:33

Kilian

/edit:
Habe den Fehler entdeckt:
Das Problem war, dass es ein zusätzliches verstecktes Datenblatt gab.
Also die nächste Frage:
Was muss ich programmieren, dass bestimmte Blätter entweder durch Nennung des Namens des Blattes oder vll auch auch durch Bezeichnung "letztes","vorletztes" etc., ausgeschlossen werden?
Grüße und vielen Dank!

AW: Excel VBA: Define UsedRange: 1. Zeile weglassen

09.03.2016 15:28:22

Daniel

Hi
im Prinzip so, beim ersten Case listest du alle Namen derjenigen Blätter auf, bei denen nichts passieren soll, den Code zum Bearbeiten schreibst du dann ins Case Else:

For i = 1 to ThisWorkbook.Worksheets.Count
Select Case Worksheets(i).Name
Case "Tabelle1", "Tabelle2", "Tabelle3"
Case Else
hier dann dein Code
End Select
Next

Gruß Daniel

Infobox / Tutorial

Excel VBA: UsedRange ohne die erste Zeile verwenden

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.
Erstelle ein neues Modul: Rechtsklicke auf "VBAProject (DeineDatei.xlsm)" und wähle Einfügen > Modul.

Füge den folgenden Code ein, um die Daten von mehreren Tabellen zu kopieren, ohne die erste Zeile einzuschließen:

Sub TabellenKopierenUntereinander()
   Dim i As Integer
   Dim Rng As Range
   Dim rng1 As Range

   With ActiveWorkbook
       For i = 2 To .Worksheets.Count
           Set Rng = .Worksheets(i).UsedRange.Offset(1, 0) ' Ignoriere die erste Zeile
           Set rng1 = Worksheets(1).Cells(Rows.Count, "B").End(xlUp)(2)
           Rng.Copy Destination:=rng1
       Next
   End With
End Sub

Führe das Makro aus, indem du auf F5 drückst oder das Makro über das Excel-Menü startest.

Häufige Fehler und Lösungen

Problem: Das Makro kopiert auch die Überschriften.
- Lösung: Stelle sicher, dass du Offset(1, 0) verwendest, um die erste Zeile zu ignorieren.
Problem: Es wird eine zusätzliche Leerzeile kopiert.
- Lösung: Verwende UsedRange.Resize(UsedRange.Rows.Count - 1).Offset(1, 0) um nur die relevanten Zeilen zu kopieren.
Problem: Daten von versteckten Blättern werden kopiert.
- Lösung: Füge eine Überprüfung hinzu, um versteckte Blätter zu ignorieren.

Alternative Methoden

Verwenden von Intersect:

Set Rng = Worksheets(i).UsedRange
Set Rng = Intersect(Rng, Rng.Offset(1, 0))

Direktes Definieren des Bereichs:

Set Rng = Range(Worksheets(i).Range("A2"), Worksheets(i).Cells.SpecialCells(xlCellTypeLastCell))

Diese Methoden können dir helfen, die UsedRange flexibler zu definieren, je nach deinen Anforderungen.

Praktische Beispiele

Hier ist ein Beispiel, wie du nur bestimmte Blätter ausschließen kannst:

For i = 1 To ThisWorkbook.Worksheets.Count
    Select Case Worksheets(i).Name
        Case "Tabelle1", "Tabelle2", "Tabelle3"
            ' Nichts tun
        Case Else
            ' Hier dein Code zur Verarbeitung
    End Select
Next

Dieses Beispiel zeigt, wie du mit Select Case gezielt bestimmte Arbeitsblätter ausschließen kannst.

Tipps für Profis

Nutze Application.ScreenUpdating = False, um die Leistung zu verbessern, während das Makro läuft.
Vergiss nicht, die Clipboard-Funktion zu leeren mit Application.CutCopyMode = False am Ende des Makros.
Verwende Columns.AutoFit, um die Spaltenbreite nach dem Kopieren automatisch anzupassen.

FAQ: Häufige Fragen

1. Wie kann ich die Anzahl der kopierten Spalten überprüfen?
Du kannst UsedRange.Columns.Count verwenden, um die Anzahl der Spalten im UsedRange zu ermitteln.

2. Warum wird die letzte Spalte nicht kopiert?
Überprüfe, ob es Leerzeilen in deiner Datenquelle gibt, die die Kopierfunktion beeinflussen könnten. Stelle sicher, dass du UsedRange korrekt definierst.

3. Wie kann ich nur bestimmte Zeilen kopieren?
Du kannst die Range-Funktion anpassen, um nur die gewünschten Zeilen zu definieren, z.B. Range("A2:A10") für die Zeilen 2 bis 10.

Beliebteste Forumthreads (12 Monate)

Excel VBA: Define UsedRange: 1. Zeile weglassen

Forumthread: Excel VBA: Define UsedRange: 1. Zeile weglassen

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Excel VBA: UsedRange ohne die erste Zeile verwenden

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Excel VBA: Define UsedRange: 1. Zeile weglassen

Forumthread: Excel VBA: Define UsedRange: 1. Zeile weglassen

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Excel VBA: UsedRange ohne die erste Zeile verwenden

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread