Dynamische Liste mehrere Tabellenblätter

Forumthread: Dynamische Liste mehrere Tabellenblätter

02.05.2019 11:51:48

alex

Hallo zusammen,
ich suche eine Lösung für folgendes Problem:
Ich will mir eine Liste ausgeben lassen, die lückenlos alle Einträge auflistet, die ein bestimmtes Kriterium erfüllen. Die Einträge sind jedoch über einige Arbeitsblätter verteilt (alle gleich aufgebaut).
Bisher mittels Makro umgesetzt, jedoch leider sehr langsam. Geht das auch mit "normalen" Formeln? Die Namen der Tabellenblätter ändern sich nicht, kann als zur Not in die Formel mit rein (ca. 30 Stück).
https://www.herber.de/bbs/user/129526.xlsx

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Dynamische Liste mehrere Tabellenblätter

02.05.2019 13:27:30

UweD

Hi
Bisher mittels Makro umgesetzt, jedoch leider sehr langsam.
Da makro sehen wir nicht, können somit auch nicht erkennen warum es evtl. längsam ist.
LG UweD

AW: Dynamische Liste mehrere Tabellenblätter

02.05.2019 13:37:59

alex

Hallo UweD,
anbei das bisherige Makro:

Option Explicit
Sub Zusammenfassen()
Dim Wks As Worksheet, loLetzte As Long
Application.ScreenUpdating = False
Worksheets("Zusammenfassung").UsedRange.Offset(1).ClearContents
For Each Wks In ThisWorkbook.Worksheets
Select Case Wks.Name
Case "Zusammenfassung", "2", "3
'hier einige Blätter die nicht bearbeitet werden sollen anführen, spart Arbeit
Case Else
With Wks
If .Range("A1") = "Bedingung" Then
'Damit hier nur relevante Blätter durchsucht werden, die das richtige Format haben
.UsedRange.Offset(1).Copy
With Worksheets("Zusammenfassung")
loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row
.Cells(loLetzte, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
End If
End With
End Select
Next Wks
End Sub

Das funktioniert gut, dauert aber gute 30 Sekunden und benötigt auch beim Öffnen der Excel-Datei einige Zeit. Die so entstandene Liste aller Einträge habe ich bisher anschließend manuell gefiltert. Nicht sehr elegant, aber hat seinen Zweck erfüllt.

AW: Dynamische Liste mehrere Tabellenblätter

02.05.2019 14:22:36

UweD

Hi
minimale Änderung: das Kopieren durch dirktes Zuweisen der Bereiche realisiert.
ob das was an Geschwindigkeit bringt, keine Ahnung

Sub Zusammenfassen()
    Dim Wks As Worksheet, loLetzte As Long, Zeilen
    Application.ScreenUpdating = False
    With Worksheets("Zusammenfassung")
        If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten 
        .UsedRange.Offset(1).ClearContents
    End With
    For Each Wks In ThisWorkbook.Worksheets
        Select Case Wks.Name
            Case "Zusammenfassung", "2", "3"
                'hier einige Blätter die nicht bearbeitet werden sollen anführen, spart Arbeit 
            Case Else
                With Wks
                    If .Range("A1") = "Bedingung" Then
                    Zeilen = .UsedRange.Rows.Count - 1
                    'Damit hier nur relevante Blätter durchsucht werden, die das richtige Format haben 
                        With Worksheets("Zusammenfassung")
                            loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row
                            .Cells(loLetzte, 1).Resize(Zeilen, 2).Value = Wks.Cells(2, 1).Resize(Zeilen, 2).Value
                        End With
                    End If
                End With
        End Select
    Next Wks
    
    With Worksheets("Zusammenfassung")
        .Columns("B:B").AutoFilter Field:=1, Criteria1:=.Range("B1")
    End With
End Sub

LG UweD

AW: alternativ zu einer VBA-Lösung ...

02.05.2019 14:01:43

neopa

Hallo Alex,
... wäre es meiner momentaner Sicht eine Pivot- oder eine PowerQery-Lösung. Eine Formellösung dürfte wohl nicht in Frage kommen, da Deine VBA-Lösung (die ich ich mir nicht weiter angeschaut habe) schon zu langsam wäre.
Für die Pivotlösung würde ich die Blätter zunächst gruppieren und die beiden Spalten Überschriften zuweisen, z.B. Wert1 und Wert2. Dann den PivotTable-Assistenten aktivieren und dort die Option: "Mehrere Konsolidierungsbereich" und "Einfache Seitenerstellung". Dann sämtliche auszuwertenden Tabellenbereich hinzufügen und dann "Zeile" in Zeilenbeschriftung und "Wert" in Berichtsfilter schieben und "Spalte" entfernen.
Nun musst Du nur noch filtern.
Gruß Werner
.. , - ...

Formeln..

02.05.2019 14:43:25

UweD

ginge schon.
Wenn du die einzelnen Bereiche (hier farbig unterschieden) groß genug wählst

Liste (2)

	A	B	C
1	Eingabe Filter	2100	Filter
2	Liste Ausgabe	20	2000
3		21	2100
4		22	2200
5		23	2300
6		24	2400
7		25	2500
8		26	2600
9		27	2700
10		28	2800
11		29	2900
12		0	0
13		0	0
14		0	0
15		0	0
16		0	0
17		0	0
18		0	0
19		0	0
20		0	0
21		30	3000
22		31	3100
23		32	3200
24		33	3300
25		34	3400
26		35	3500
27		36	3600
28		37	3700
29		38	3800
30		39	3900
31		40	2100
32		0	0
33		0	0
34		0	0
35		0	0
36		0	0
37		0	0
38		0	0
39		0	0
40		0	0
41		0	0
42		40	4000
43		41	4100
44		42	4200
45		43	4300

verwendete Formeln
Zelle	Formel	Bereich	N/A
B2:C20	=Tabelle2!A2
B21:C41	=Tabelle3!A2
B42:C45	=Tabelle4!A2

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 25.14 einschl. 64 Bit

Als Ergänzung dann noch.
In den Codebereich der Tabelle
Microsoft Excel Objekt Tabelle6

Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Not Intersect(Range("B1"), Target) Is Nothing Then 
        Columns("C:C").AutoFilter Field:=1, Criteria1:=Target 
    End If 
End Sub

Wenn du dann B1 auf 2100 änderst, werden nur die 2100 er angezeigt.
Willst du alle , ausser die Nullen sehen, gibts du in B1 &GT0 ein
LG UweD

Beliebteste Forumthreads (12 Monate)

Dynamische Liste mehrere Tabellenblätter

Forumthread: Dynamische Liste mehrere Tabellenblätter

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

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread