Office: Makro: Wert aus Tabellenblatt in anderes Tabellenblatt kopieren, Benennung variiert

Hallo liebes Forum!
Zunächst einmal: ich bin neu hier und ein totaler Makro-Noob – also bitte Rücksicht nehmen. Das Problem fängt schon damit an, dass ich nicht genau weiß wonach ich googlen soll. Mein Problem ist bestimmt nicht neu und mit Sicherheit schon hundert Mal behandelt worden, aber scheinbar nutze ich die falschen Stichwörter.

Programm-Version: Excel 2010

Mein Problem: Ich möchte aus einer variablen Anzahl Tabellenblättern jeweils einen Zahlenwert (zwischen 0 und zehn) kopieren und dann auf einem Übersichts-Tabellenblatt einfügen. Die Spalte in welche die Werte eingetragen werden sollen beginnt bei B2 und kann beliebig fortgesetzt werden (B3, B4 usw.).
Die Reihenfolge meiner Tabellenblätter im Dokument sieht so aus: Übersichtsblatt, Blanko-Blatt, S1, S2, S3 …
Die Blätter S1, S2 usw. sind alle gleich aufgebaut, enthalten nur unterschiedliche Zahlen. Den Wert aus Zelle K14 dieser Blätter möchte ich auf dem Übersichtsblatt anzeigen lassen. Der Wert ist auch immer in der gleichen Zelle K14.
Die Bezeichnung der Blätter S1, S2 usw. variiert. D.h. die heißen auch mal P1 oder M4.
Manuell bekomm ich das hin, dass mir die Werte angezeigt werden (z.B. ='P1'!K14 oder ='M2'!K14). Allerdings ist das ab einer bestimmten Blattanzahl etwas mühsam. Deswegen hätte ich am liebsten ein Makro was die Werte kopiert und in die Übersichtstabelle einfügt.
Dummerweise habe ich von Makro-Programmierung so viel Ahnung wie die Kuh vom Fallschirmspringen. Meine Suchen ergaben verschiedene Ergebnisse in Form von Schleifen und sonstigem. Allerdings hört es bei mir ja schon mit der Auswahl des Tabellenblattes auf …
Was ich mir schon erschlossen habe, ist die Auswahl der Zelle im jeweiligen Tabellenblatt und das Kopieren selbiger:

Code:

Range("K14").Select 
Selection.Copy

Hier darf jetzt mal kurz gelacht werden
Allerdings hilft mir das ja auch nicht wirklich weiter, weil bis dato der Wert ja nur in den Zwischenspeicher gewandert ist. Und noch weit davon entfernt ist, in meiner Übersichtstabelle zu erscheinen.

Wenn ich den manuellen Vorgang mit dem Makro-Recorder aufzeichne (ich starte vom Übersichtsblatt aus), sieht das wie folgt aus:

Code:

Sub Kritikalitaetswert()
'
' Kritikalitaetswert Makro
' Kritikalitätswert aus anderen Tabellenblättern entnehmen und in die Übersichtstabelle kopieren.
'
'
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "='P1'!R[12]C[9]"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "='P2'!R[11]C[9]"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "='P3'!R[10]C[9]"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "='P4'!R[9]C[9]"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "='I1'!R[8]C[9]"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "='I2'!R[7]C[9]"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "='I3'!R[6]C[9]"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "='M2'!R[5]C[9]"
    Range("B10").Select
End Sub

Das macht dann auch ganz brav was es soll, aber halt nur für die Tabellenblätter die P1-P4, I1-I3 und M2 heißen. Es sollen aber alle K14-Werte aller Tabellenblätter aufgeführt werden – z.B. auch wenn ich ein weiteres Tabellenblatt einfüge.

Hilfe wäre toll – und bitte auf gaaaaanz einfachem Niveau erklären.
Vielen Dank im Voraus!

 

Hallo,

kopiere einfach das nachfolgende Makro und füge es unterhalb des Makros ein, das du mit dem Makrorekorder erzeugt hast:

Code:

Sub SpeichereAlle_K14()

  'Teile dem Compiler mit,
  'was die Variablen Ws und Zelle bedeuten sollen:
  Dim Ws As Worksheet  'Ws soll auf ein Arbeitsblatt zeigen
  Dim Zelle As Range   'Zelle soll auf einen Zellbereich zeigen
  
  'Zeige der Reihe nach mit der Variablen Ws
  'auf alle Arbeitsblätter der Arbeitsblattliste der aktiven Arbeitsmappe:
  For Each Ws In ActiveWorkbook.Worksheets
    'und überprüfe ob ...
    If Ws.Name = "Übersichtsblatt" Then
      '... es sich beim Arbeitsblatt, auf das Ws gerade zeigt,
      'um das Arbeitsblatt mit Namen "Übersichtsblatt" handelt, dann:
      'speichere in der Variablen Zelle die Zelladresse "Übersichtsblatt!B2"
      Set Zelle = Ws.Range("B2")
    ElseIf Ws.Name = "Blanko-Blatt" Then
      '... es sich beim Arbeitsblatt, auf das Ws gerade zeigt,
      'um das Arbeitsblatt mit Namen "Blanko-Baltt" handelt, dann:
      'Tue einfach nichts Besonderes
    Else
      '... es sich um ein anderes Arbeitsblatt handelt, dann:
      'erzeuge eine einfache Zellbezugsformel:  "=Blatt!K14"
      'und speichere sie in die Formula-Eigenschaft
      'der gerade aktuellen Zelle der B-Spalte des Übersichtsblattes
      Zelle.Formula = "=" & Ws.Name & "!K14"
      'Nimm die gerade aktuelle Zelle der B-Spalte des Übersichtsblattes
      'und speichere in ihr die Zelladresse der Zelle, die 1 Zeile unterhalb von ihr ist:
      Set Zelle = Zelle.Offset(1, 0)
    End If
    'Zeige auf das nächste Arbeitsblatt
    'in der Arbeitsblattliste der aktiven Arbeitsmappe
  Next Ws
  
End Sub

Dieses Makro setzt voraus,
1) dass das Arbeitsblatt, in das du Formeln einfügst, "Übersichtsblatt" heißt und dass dieses das 1. Blatt in der Arbeitsmappe ist;
2) ferner übergeht das Makro das Arbeitsblatt, das "Blanko-Blatt" heißt, ohne dafür eine K14-Formel zu erzeugen.
3) Für alle anderen Arbeitsblätter erzeugt das Makro die Formel zur Abfrage der Zelle K14 und fügt sie untereinander in Spalte B ab Zelle B2 des Übersichtsblattes ein.

Ich habe jede Menge Kommentarzeilen ins Makro eingefügt, sodass es halbwegs verständlich sein sollte, was das Makro macht.

 

Hallo Exl121150,

zunächst vielen, vielen Dank für deine Hilfestellung und Mühe!
Leider hat das Ganze bei mir so nicht funktioniert - ich schätze mal, dass ich das nicht korrekt übertragen habe. Ich habe jetzt von anderer Stelle Hilfe bekommen und der Code funktioniert. Der Vollständigkeit halber hier zum Nachlesen:

Code:

Sub Kritikalitaetswert()
iCnt = 2
For Each Blaetter In Sheets
Cells(iCnt, 2) = Blaetter.Range("K14")
iCnt = iCnt + 1
Next
End Sub

Dadurch wird mir zwar auch der Wert des Blanko-Blattes angezeigt, aber den habe ich jetzt ganz rustikal und simpel ausgeblendet.

Dennoch vielen Dank nochmal :)