Zellenwerte aus unterschiedl. Sheets in sep. Sheet einfügen

Moderator: ModerationP

Zellenwerte aus unterschiedl. Sheets in sep. Sheet einfügen

Beitragvon mahowe » 16. Okt 2021, 12:53

Hallo Community,
habe mich wieder bei einer trivialen Anforderung festgebissen und ratlos aufgegeben

Meine Anforderung::
Ich habe verschiedene Arbeitsblätter (01_Arbeitsblatt_A, 02_Arbeitsblatt_B, 03_Arbeitsblatt_C, 04_Arbeitsblatt_D, ...) mit identischen Aufbau und Zelleninhalten.
Im Arbeitsblatt "Zusammenfassung_Werte" werden die erstellten Arbeitsblätter über einen Makro-Button ab B8 dann aufgelistet.(Siehe Bild)

AB_Zusammenfassung Werte.jpg


Das Makro zum Auflisten der Arbeitsblätter:
Code: Alles auswählen
Sub Arbeitsblaetter_auflisten()
'In Zeile 7 Spalte 2 kommt Überschrift
   Dim z As Long, s As Long, i As Long
      s = Selection.Column
     z = Selection.Row
     Cells(7, 2) = "Arbeitsblätter dieser Arbeitsmappe"
     z = 7 + 1   
    'Hier werden alle vorhandenen Arbeitsblätter aufgelistet
     For i = 1 To Sheets.Count
         Cells(z, 2) = Sheets(i).Name
         z = z + 1
     Next i
End Sub


Jetzt müssen die Zellenwerte (F2-F4) aus den Arbeitsblättern in den Summen-Zellen (E8-H11) in Zusammenfassung_Werte dann angezeigt werden (Habe ich mit Formeln und händischen Anpassen der einzelnen Zellen gemacht. Beispiel der Formeleinträge aus Spalte E8-E11:
=(SUMME(INDIREKT("'"&B8&"'!F2")))
=(SUMME(INDIREKT("'"&B9&"'!F2")))
=(SUMME(INDIREKT("'"&B10&"'!F2")))
=(SUMME(INDIREKT("'"&B11&"'!F2")))

Wunsch(e):
- Wäre super, wenn man in den o.g. Formeln statt B8,B9,B10,B11 nur "$B" o.ä. für den direkten Bezug zu der Spalte B eingibt.
So könnte man die Formeln einfach händisch nach unten kopieren. Habe es nicht geschafft.
- Viel besser wäre es natürlich, wenn die Formeln neben erzeugten Arbeitsblättern automatisch hinzugefügt werden würden. Ein Ansatz wäre hier schon hilfreich.
- Wenn dann nur die Arbeitsblätter beginnend mit einer 01_.., 02_.., 03_..selektieren und in der "Zusammenfassung_Werte" aufgelistet werden würden wäre es ein Volltreffer.

Vorsichtshalber lege ich meine Beispiel_Datei_1.xlsm bei, da meine textlichen Ausführungen meist verwirrt sind.
Danke schon mal an die exzellente Community, die immer wieder hilft.
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
mahowe
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 178
Registriert: 23. Jan 2018, 20:54

Re: Zellenwerte aus unterschiedl. Sheets in sep. Sheet einfü

Beitragvon 1Matthias » 16. Okt 2021, 14:32

Moin!
Hier mal ein paar Ideen - kein fertiger Code.
Für die Auflistung nur der Blätter.
Prüfe in der Schleife den Dateinamen. Bspw. bei "_" splitten und dann schauen, ob der erste Teil deine Zahl ist. Wenn ja, dann einfügen.
Und wenn du dann schonmal bei dem Namen vom Sheet bist, füge damit gleich einen Verweis auf die entsprechende Zelle ein.
Bspw so:
Code: Alles auswählen
test= Sheets(i).Name
Cells(1, 2).Formula = "=" & test & "!A1"

Denn in der Schleife mit auf deine Zelle anpassen - anstelle des A1.

Schau mal, ob dich das ein wenig weiterbringt. Ansonsten nochmal melden.
VG
1Matthias
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 972
Registriert: 15. Aug 2017, 18:36

Re: Zellenwerte aus unterschiedl. Sheets in sep. Sheet einfü

Beitragvon mahowe » 16. Okt 2021, 18:24

Sorry Matthias,
bin doch leicht überfordert zu deinen Ausführungen zu Namensprüfung und Zellübernahme.
Liegt aber an mir, dass ich deinen Beispiel-Code im Ansatz nicht begreife.

Mahowe
mahowe
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 178
Registriert: 23. Jan 2018, 20:54

Re: Zellenwerte aus unterschiedl. Sheets in sep. Sheet einfü

Beitragvon PIVPQ » 16. Okt 2021, 19:56

Hallo

Ein Vorschlag von mir ohne VBA mit PQ
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Viele Grüße
PIVPQ
PIVPQ
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 133
Registriert: 19. Dez 2020, 15:11

Re: Zellenwerte aus unterschiedl. Sheets in sep. Sheet einfü

Beitragvon mahowe » 16. Okt 2021, 21:23

Hallo PIVPQ,

danke für die Antwort.
Ich erweitere/lösche ja die Arbeitsblätter dynamisch. D.h. es können einmal 6 und danach 34 Blätter sein.
Die Arbeitsblätter erstelle ich mit einem Makro aus einem Master-Arbeitsblatt mit eigenem Namen etc.
Händisch möchte ich so wenig wie möglich eingreifen. Deshalb VBA

Gruß MAHOWE
mahowe
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 178
Registriert: 23. Jan 2018, 20:54

Re: Zellenwerte aus unterschiedl. Sheets in sep. Sheet einfü

Beitragvon PIVPQ » 17. Okt 2021, 07:57

Hallo

Um es kurz zu fassen zusagen:
A) Die Aufteilung in einmal 6 dann 34 extra Tabellen und später ein Teil der Tabellen zu löschen ist in meinen Augen größter Müll.
Excel hat genügend Spalten und Zeilen!!
B) In deiner Datei die du hier zeigst ist nix zu sehen von so vielen Arbeitsblätter.
C) Eine VBA Lösung da zusammen zu reimen ist mir die Zeit zu schade für, von daher steige ich aus und viel Spaß noch.
Viele Grüße
PIVPQ
PIVPQ
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 133
Registriert: 19. Dez 2020, 15:11

Re: Zellenwerte aus unterschiedl. Sheets in sep. Sheet einfü

Beitragvon 1Matthias » 17. Okt 2021, 12:21

Moin!
Also hier mal eine Möglichkeit in VBA. Es werden erst alle Einträge gelöscht. Dann werden die BLätter neu eingelesen. DAbei werden die Links auf die entsprechenden Zellen gesetzt und die Summe unten eingefügt.
Code: Alles auswählen
Private Sub CommandButton1_Click()
Dim ende As Long
Dim blatt, aufbau, index As Long
Dim zeile As Long


'letzte Zeile in Blatt Zusa
ende = Cells(Rows.Count, 2).End(xlUp).Row

'alte Einträge löschen
If ende > 7 Then Cells(8, 2).Resize(ende - 7, 7).ClearContents

zeile = 8
'durch alle Blätter gehen, worksheets sind dabei nur die Tabellenblätter
'sheets sind alle Blätter - auch Diagramm
For index = 1 To Worksheets.Count
    aufbau = Split(Worksheets(index).Name, "_")
   
    'prüfen ob Blatt passt
    'Prüfung ggf. anpassen, prüfe nur ob vorne ein als Zahl interpretierbarer Wert steht und es einen _ gab
    If UBound(aufbau) > 0 And IsNumeric(aufbau(0)) Then
        Cells(zeile, 2) = Worksheets(index).Name
        'wenn es mehr Werte sind, könnte man es auch in einer Schleife machen
        Cells(zeile, 5).Formula = "=" & Worksheets(index).Name & "!F2"
        Cells(zeile, 6).Formula = "=" & Worksheets(index).Name & "!F3"
        Cells(zeile, 7).Formula = "=" & Worksheets(index).Name & "!F4"
        Cells(zeile, 8).Formula = "=" & Worksheets(index).Name & "!F5"
       
       
        'zeile um eins erhöhen, für nächsten Durchlauf
        zeile = zeile + 1
    End If
   
Next

Cells(zeile, 2) = "Zusammenfassung_Werte"
'Wenn da auch die summe rein soll, dann das hier lassen, sonst auskommentieren
If zeile > 8 Then
    Cells(zeile, 5).FormulaLocal = "=SUMME(E8:E" & zeile - 1 & ")"
    Cells(zeile, 6).FormulaLocal = "=SUMME(F8:F" & zeile - 1 & ")"
    Cells(zeile, 7).FormulaLocal = "=SUMME(G8:G" & zeile - 1 & ")"
    Cells(zeile, 8).FormulaLocal = "=SUMME(H8:H" & zeile - 1 & ")"
End If
End Sub


VG
1Matthias
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 972
Registriert: 15. Aug 2017, 18:36

Re: Zellenwerte aus unterschiedl. Sheets in sep. Sheet einfü

Beitragvon mahowe » 17. Okt 2021, 16:49

Hallo Matthias,
herzlichen Dank.
Es funktioniert nach ein paar kleinen Anpassungen (hat nur mit meiner Datei zu tun) bestens.
Habe schon ein paar Stunden damit verbracht um mir einen Workaround zu basteln. Teilerfolge waren da und gelernt habe ich dabei auch noch.
Aber mit deiner Lösung kein Vergleich. Die hat alle Anforderungen abgedeckt

Mahowe
mahowe
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 178
Registriert: 23. Jan 2018, 20:54


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: Google [Bot] und 45 Gäste