Tabelle um ein Jahr erweitern

Forumthread: Tabelle um ein Jahr erweitern

04.12.2022 22:31:13

Volker

Hallo,
letztes Jahr habe ich den richtigen Tipp hier im Forum bekommen wie man die Kosten zwischen mehreren Gas-Ableseterminen über die Monate und Jahre verteilt - vielen Dank hierfür. Nun wird das Thema durch die steigenden Gas-Preise spannend. Seitdem habe ich das Sheet stetig erweitert - siehe Anlage - vielleicht hat ja jemand das gleiche Thema.
Zwei kleinere VBA-Programme blenden Zeilen und Spalten aus und ein, damit man einen Ausdruck machen kann bzw. nur einzelne Jahre sieht.
Kann mir jemand einen Tipp geben, wie ich die zwei folgenden Probleme lösen kann:
a) Per Buttom soll das Sheet um ein "Jahr" (bzw. um 12 Spalten) erweitert werden. Normalerweise markiere ich die vorletzte Spalte und "ziehe" diese dann einfach um 12 Monate weiter.
b) Der Code des VBA-Programms Spaltenansicht (Dropbox in Zelle C2) wird nicht geändert, wenn 12 neue Spalten ergänzt wurden. Wie kann man hier die letzte Spalte dynamische in VBA ergänzen.
Z.B.:
'Prüfen, ob "2019" in Zelle C2 steht und blende die Monate vor 12/2018 und nach 01/2020 aus
ElseIf Range("C2").Value = "2019" Then
Columns("j:t").EntireColumn.Hidden = True
Columns("ai:bs").EntireColumn.Hidden = True
D.H. für "bs" müsste die Variable "xLastcolumn" (siehe Modul1) stehen.
Meine VBA-Kenntnisse sind leider begrenzt, so dass ich die beiden VBA-Routinen nicht zusammen bekomme.
Dank und Gruß
Volker
https://www.herber.de/bbs/user/156521.xlsm

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Tabelle um ein Jahr erweitern

04.12.2022 23:39:28

ralf_b
AW: Tabelle um ein Jahr erweitern

05.12.2022 11:59:52

Volker
AW: Tabelle um ein Jahr erweitern

05.12.2022 13:53:49

ralf_b
AW: Tabelle um ein Jahr erweitern

05.12.2022 16:48:10

Volker
AW: Tabelle um ein Jahr erweitern

05.12.2022 21:04:56

ralf_b

AW: Tabelle um ein Jahr erweitern

04.12.2022 23:39:28

ralf_b

dieser Code ersetzt deine festen Zeilenzuweisungen für das Ausblenden


'Spaltenansicht
'Prüfen, ob wir in Zelle c2 sind
If Not Intersect(Target, Range("c2")) Is Nothing Then
Dim rng As Range, i&
'Spalten einblenden
Cells.EntireColumn.Hidden = False
Select Case Target.Value
Case "Alle"
Case 2018 To 2030
For i = Columns("J").Column To Cells(6, Columns.Count).End(xlToLeft).Column
If Cells(6, i).Value  Target.Value Then
If rng Is Nothing Then
Set rng = Cells(6, i)
Else
Set rng = Union(rng, Cells(6, i))
End If
End If
Next
If Not rng Is Nothing Then rng.EntireColumn.Hidden = True
Case Else
End Select
End If

dieser code füllt den dropdown mit den Jahreszahlen die beim Aktivieren des Blattes in Zeile 6 stehen


Private Sub Worksheet_Activate()
Dim s$, i&
For i = Application.WorksheetFunction.Min(Rows(6)) To Application.WorksheetFunction.Max(Rows(6))
s = s & "," & i
Next
With Range("C2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Alle" & s
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

AW: Tabelle um ein Jahr erweitern

05.12.2022 11:59:52

Volker

Hallo Ralf,
ich habe die Routinen ausprobiert und beide funktionieren so wie ich mir das vorgestellt habe. Vielen Dank! Die Liste der DropBox Werte automatisch zu generieren ist elegant und ich werde mir das für andere Excel Sheets merken.
Hat jemand noch eine Lösung für mein Problem a)?
Ich habe die Excel Datei upgedatet und die Wasser-Abrechnung angefügt, um das "Aktivieren" des Blattes auszuprobieren.
Dank und Gruß
Volker
https://www.herber.de/bbs/user/156530.xlsm

AW: Tabelle um ein Jahr erweitern

05.12.2022 13:53:49

ralf_b

man kann natürlich auch ein anderes Event-Makro nutzen. Je nachdem was besser passt.
Solltest du nun 2 Blätter in der Datei haben, die identischen Aufbau haben und in den Zellen auch die passende Datenüberprüfung haben, dann kann man den Code in das Workbook-Codemodul umlagern.
in DieseArbeitsmappe


Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
If Left(sh.Name, 3) = "Gas" Or Left(sh.Name, 3) = "Was" Then
Select Case Target.Address(0, 0)
Case "C2", "I5"
fillvalidation sh, sh.Range("C2,I5")
Case "F2"
If Target.Value = "Group" Then
sh.Outline.ShowLevels RowLevels:=1
sh.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
'Prüfen, ob "Ungroup" in Zelle F2 steht
ElseIf Target.Value = "Ungroup" Then
sh.Outline.ShowLevels RowLevels:=3
sh.Outline.ShowLevels RowLevels:=0, ColumnLevels:=3
End If
End Select
End If
End Sub

dazu in ein Allgemeines Modul die angepasste Validation sub


Sub fillvalidation(sh As Worksheet, rng As Range)
'Spaltenansicht
'Dieser Code füllt die Dropdown-Box mit den Jahreszahlen die beim Aktivieren des Blattes in Zeile 6 stehen
Dim s$, i&
For i = Application.WorksheetFunction.Min(sh.Rows(6)) To Application.WorksheetFunction.Max(sh.Rows(6))
s = s & "," & i
Next
With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Alle" & s             'füge "Alle" noch zur Liste hinzu
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Das Problem a) ist schon etwas aufwendiger,oder?
Es ist nicht damit getan 12 Spalten zuzufügen. Was ist mit den Summenformeln und neuen Zeilen in den Eingabebereichen "A-I"?

AW: Tabelle um ein Jahr erweitern

05.12.2022 16:48:10

Volker

Hallo Ralf,
ich habe 4 Blätter, die gleich aufgebaut sind: Gas-, Wasser-, Strom-Abrechnung und Betriebskosten-Detail.
Ich habe das wie du oben vorgeschlagen hast auch so umgesetzt. Doch muss ich nun beim Auswählen von Group / Ungroup zweimal aus der Zelle F2 raus- und rein-springen, bevor die Aktion durchgeführt wird. Was mache ich falsch?
Dank und Gruß
Volker
https://www.herber.de/bbs/user/156536.xlsm

AW: Tabelle um ein Jahr erweitern

05.12.2022 21:04:56

ralf_b

ok, hier die Anpassung für deine Blätter. Erstmal so das es bei allen mit dem Ausblenden und Gruppieren klappt. In den einzelnen Blättern st kein Code notwendig.
https://www.herber.de/bbs/user/156545.xlsm

Schritt-für-Schritt-Anleitung

Um eine bestehende Excel-Tabelle um ein Jahr zu erweitern, kannst du die folgenden Schritte befolgen:

Markiere die vorletzte Spalte deiner Tabelle.
Ziehen die Markierung um 12 Spalten nach rechts, um die Tabelle um 12 Monate zu verlängern.

Wenn du dies automatisieren möchtest, kannst du ein VBA-Makro verwenden. Hier ist ein Beispiel für ein einfaches Makro:

Sub TabelleErweitern()
    Dim lastCol As Long
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Columns(lastCol + 1).Resize(, 12).Insert Shift:=xlToRight
End Sub

Füge die Summenformeln in den neuen Spalten hinzu, um die korrekten Werte zu berechnen.

Häufige Fehler und Lösungen

Fehler: Die neuen Spalten erscheinen nicht korrekt.
- Lösung: Stelle sicher, dass du die richtigen Formeln in den neuen Spalten anwendest. Überprüfe auch, ob die Datenvalidierung in den neuen Zellen korrekt gesetzt ist.
Fehler: Das VBA-Makro funktioniert nicht wie erwartet.
- Lösung: Achte darauf, dass das Makro in dem richtigen Modul gespeichert ist und dass die richtigen Zellreferenzen verwendet werden.

Alternative Methoden

Wenn du deine Excel-Tabelle nicht manuell erweitern möchtest, gibt es auch alternative Methoden:

Verwende eine Excel-Vorlage. Du kannst eine bestehende Vorlage für ein neues Jahr kopieren und die Daten entsprechend anpassen.
Nutze die Funktion "Tabellenbereich erweitern". Achte darauf, dass deine Tabelle als "Excel-Tabelle" formatiert ist. Wenn du neue Daten hinzufügst, wird der Tabellenbereich automatisch angepasst.

Praktische Beispiele

Hier sind einige praktische Beispiele, wie du deine Excel-Tabelle um ein Jahr erweitern kannst:

Jahresabrechnung für Gas: Wenn du eine Tabelle für Gas-Abrechnungen hast, kannst du die neuen Daten für das nächste Jahr ganz einfach hinzufügen und die Formeln anpassen.
Überwachung der Kosten: Erstelle eine Übersicht über die monatlichen Kosten und erweitere die Tabelle jedes Jahr um eine Spalte, um die neuen Werte einzutragen.

Tipps für Profis

Nutze Pivot-Tabellen, um die Daten für verschiedene Jahre zu analysieren. Dadurch wird das Arbeiten mit großen Datenmengen erheblich erleichtert.
Automatisiere die Datenvalidierung für neue Jahre, indem du VBA-Makros nutzt, die die Dropdown-Listen automatisch aktualisieren, sobald neue Spalten hinzugefügt werden.
Denke darüber nach, Datenüberprüfungen in deinen neuen Spalten zu implementieren, um sicherzustellen, dass nur gültige Daten eingegeben werden.

FAQ: Häufige Fragen

1. Wie kann ich eine bestehende Excel-Tabelle um eine Spalte erweitern?
Du kannst eine neue Spalte hinzufügen, indem du mit der rechten Maustaste auf die Spaltenüberschrift klickst und "Einfügen" wählst.

2. Was muss ich tun, wenn sich die Datenstruktur ändert?
Stelle sicher, dass alle Formeln und Verweise in den neuen Spalten und Zeilen aktualisiert werden, um die korrekten Berechnungen zu gewährleisten.

3. Wie kann ich den Tabellenbereich erweitern?
Markiere die gesamte Tabelle und ziehe den kleinen Anfasser in der unteren rechten Ecke nach unten oder zur Seite, um den Tabellenbereich zu erweitern.

4. Kann ich VBA verwenden, um die Tabelle automatisch zu erweitern?
Ja, du kannst VBA verwenden, um die Tabelle automatisch um ein Jahr zu erweitern, indem du ein entsprechendes Makro schreibst.

Beliebteste Forumthreads (12 Monate)

Tabelle um ein Jahr erweitern

Forumthread: Tabelle um ein Jahr erweitern

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

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial