Blattregister mit Makros automatisch anpassen

[WillWissen]

Hi Wastl,

Datei über PN? Würde ich nicht gut finden* - und bestimmt auch nicht nachfolgende User, die sich ebenfalls "ein Bild machen" wollen. Besser wäre es, dem TE mitzuteilen, wie die Helfer sich die Beispieltabelle vorstellen.

* = über PN können keine Anhänge verschickt werden.

[munez94]

Hi,

im Anhang habe ich die Datei nochmal mit Beipsieldaten gefüllt. nun ist es vllt einfacher zu verstehen.
Die einzenlen Blattregister zu dem jeweiligen Gebäude werden automatisch angelegt wenn in der Matrix "Gebäudeliste" etwas hinzugefügt wird. Diese Blattregister sind immer nach dem einen Schema aufgebaut wie ch die Makros aufgenommen habe. Es geht darum dass ich in der Matrix Einstellungen Zeitraum die summierten Daten bekomme. Das funktioniert auch soweit. Mein einziges Promblem ist, dass dort immer mal wieder neue Energieträger hinzukommen. Jetzt versuche ich es so einzurichten, dass man dies nur Im Layout ändern muss und das Tool danach alle angelegten Blattregister zu den Gebäuden kontolliert und verbessert oder die neu Anlegungen dirket so umsetzt. (Nur Blattregister zu den Gebäuden), ausgenommen Einstellungen, Gebäudeliste un Diagramm.

Viele Grüße munez94

[Wastl]

Moin

ich brauche noch ein paar Werte:

• von wievielen Sheets/Gebäuden reden wir hier?
  
• Die Gebäudeliste hat vorbereiteten Rahmen bis 300, kommen tatsächlich nachher 300 Gebädue, also auch sheets in die Datei? oder mehr?
  

Bei mehr als - sagen wir mal 10 Gebäuden - würde ich von diesem Layout weg wollen und mir was besseres ausdenken
---
Was spricht dagegen, im neuen Sheet für jeden Eintrag in den Reihen 3/9/17 den Eintrag aus sheet "Datenblatt Layout" per Formel zu holen?

='Datenblatt Layout'!A3
und nach Rechts ziehen
bzw wenn du es schön haben willst
=WENN('Datenblatt Layout'!A3="";"";'Datenblatt Layout'!A3)


auf diese Weise werden alle Änderungen im Layout automatisch auf alle Gebäudesheets übernommen und dürfte die Performance auch nicht belasten.
---
in K3 der Gebäudeliste hilft dir die Formel besser als der aktuell falsche Eintrag "5"
=TEILERGEBNIS(3;A1:A10000)+2

---
Deinem Code nach planst du in anderen Dimensionen:

Code:

Sub Tabellenblatt_ergänzen()

Application.ScreenUpdating = False
Dim x As Long
Dim Startwert As Long
Dim Endwert As Long

Startwert = Worksheets("Gebäudeliste").Cells(2, 11).Value
Endwert = 100000000

 
For x = Startwert To Endwert Step 1
   If Worksheets("Gebäudeliste").Cells(x, 1).Value = "" Then
        Exit For
    End If
    ThisWorkbook.Worksheets.Add.Name = Worksheets("Gebäudeliste").Cells(x, 1).Value
    ActiveSheet.Move After:=Sheets("Gebäudeliste")
    Call Tabellenblätter_ergänzen
...

Endwert = 100.000.000
Gehts noch?

Ich habe vor vielen Jahren einen Versuch bei 1500 abgebrochen beim Testen, bei wievielen Tabellenblättern Excel abstürzt.
Es ist nicht abgestürzt.
Aber ich denke, 100 Millionen Blätter ist schon etwas viel

[munez94]

Moin

ich brauche noch ein paar Werte:

• von wievielen Sheets/Gebäuden reden wir hier?
  
• Die Gebäudeliste hat vorbereiteten Rahmen bis 300, kommen tatsächlich nachher 300 Gebädue, also auch sheets in die Datei? oder mehr?
  

 
Hi Wastl,
vielen Dank dafür, das hat mir schon mal weitergeholfen. Bezügüglich Gebäudeanzahl, es geht hier um mehrere Projekte wo dann das Grundtool verwendet wird. Leider kann es von 10 bis tatsächlich 300 Gebäuden variieren, wo zu jedem ein Datenblatt für die Daten ertstellt werden muss (geschieht bereits).
----------------------------------

=WENN('Datenblatt Layout'!A3="";"";'Datenblatt Layout'!A3)

Auch das hat mir geholfen, da es so alles übernimmt was das Layout beinhaltet.
Mein Porblem bezüglich dieser Formel, wie kann ich diese Formel in mein Modul2 einbauen, dass die Formel automatisch in den neu erstellten Blattregister erscheint. Wie es bei der Formel =Summeprodukt der Fall, in meinem Tool, ist.
Wenn ich das noch einbauen kann bzw. ein Tipp bekomme wie man das macht, dann wäre auch das Thema geklärt und mir wurde sehr weitergeholfen.

-----------------
Die 1Mio war in der Probephase ohne Hintergrund eingetippt worden. Ist bereits geändert.


Viele Grüße munez94

[Wastl]

hi,

Die 1Mio war in der Probephase ohne Hintergrund eingetippt worden. Ist bereits geändert.

die Zahl hatte 8 Nullen

Mein Porblem bezüglich dieser Formel, wie kann ich diese Formel in mein Modul2 einbauen

1. Trage diese Formel in Excel an die gewünschte Stelle
   
2. Öffne den VBA-Editor
   
3. Öffne den Direktbereich (z.B.: mit Tastenkombination Strg+g
   
4. Schreibe da hinen mit Fragezeichen: 
   ? activecell.Formula
   
5. Drücke Enter
   direkt darunter schreibt es dir die Formel in der von VBA-verarbeitbaren Form hinein. Musste nur noch kopieren und an der richtigen Stelle in deinem Code unterbringen.
   

Bezügüglich Gebäudeanzahl, es geht hier um mehrere Projekte wo dann das Grundtool verwendet wird. Leider kann es von 10 bis tatsächlich 300 Gebäuden variieren, wo zu jedem ein Datenblatt für die Daten ertstellt werden muss (geschieht bereits).

Ich halte die Vorgehensweise dennoch für falsch.
In dem letzten Post stehen bei einem Gebäude für 2  (von aktuell 23 Möglichen) Energieträgern Werte von täglichen Mengen.
Und zwar händisch eingetragen.
Wenn ich mir nun vorstelle, ich selbst müsste diese Datei täglich pflegen, also bei 300 Tabellenblättern täglich bei 23 Energieträgern Werte eintragen…
… mich würde der Schlag treffen, oder ich würde auf der Stelle kündigen.

Tipp:
Überlege dir eine Lösung, wie du das alles in ein einziges Tabellenblatt unterbringst.
Immerhin haste 16.384 Spalten zur Verfügung, - ist später dann auch viel einfacher auszuwerten

[munez94]

1. Trage diese Formel in Excel an die gewünschte Stelle
   
2. Öffne den VBA-Editor
   
3. Öffne den Direktbereich (z.B.: mit Tastenkombination Strg+g
   
4. Schreibe da hinen mit Fragezeichen: 
   ? activecell.Formula
   
5. Drücke Enter
   direkt darunter schreibt es dir die Formel in der von VBA-verarbeitbaren Form hinein. Musste nur noch kopieren und an der richtigen Stelle in deinem Code unterbringen.
   

Hi,

Code:

Sub Tabellenblätter_ergänzen()
'
' Tabellenblätter_ergänzen Makro
'
Dim intI As Integer
'
  Range("A2").FormulaR1C1 = "Ergebnisfelder Zeitraum 1"
  Range("A8").FormulaR1C1 = "Ergebnisfelder Zeitraum 2/Vergleich"
  For intI = 1 To 21
      Cells(3, intI) = Choose(intI, "Gas", "Wärme", "Fernwärme", "Strom", "Heizöl", "Wasser", "Kompressor", "Druckluft", "Trockner", "Kompressor Kühlung", "HGT Aktuell", "HGT Referenz", "Wärme bereinigt", "Strom/bereinigt", "Gas/Kennwert", "Wärme/Kennwert", "Fernwärme/Kennwert", "Strom/Kennwert", "Wärme bereinigt/Kennwert", "Druckluft/Kennwert", "Wasser/Kennwert")
  Next intI
  For intI = 1 To 21
      Cells(9, intI) = Choose(intI, "Gas", "Wärme", "Fernwärme", "Strom", "Heizöl", "Wasser", "Kompressor", "Druckluft", "Trockner", "Kompressor Kühlung", "HGT Aktuell", "HGT Referenz", "Wärme bereinigt", "Strom/bereinigt", "Gas/Kennwert", "Wärme/Kennwert", "Fernwärme/Kennwert", "Strom/Kennwert", "Wärme bereinigt/Kennwert", "Druckluft/Kennwert", "Wasser/Kennwert")
  Next intI
  With ActiveWindow
      .SplitColumn = 0
      .SplitRow = 15
  End With
  ActiveWindow.FreezePanes = True
'    ActiveWindow.SmallScroll Down:=-6
  Range("A17").FormulaR1C1 = "Datum"
  For intI = 1 To 21
      Cells(17, intI + 1) = Choose(intI, "Gas", "Wärme", "Fernwärme", "Strom", "Heizöl", "Wasser", "Kompressor", "Druckluft", "Trockner", "Kompressor Kühlung", "HGT Aktuell", "HGT Referenz", "Wärme bereinigt", "Strom/bereinigt", "Gas/Kennwert", "Wärme/Kennwert", "Fernwärme/Kennwert", "Strom/Kennwert", "Wärme bereinigt/Kennwert", "Druckluft/Kennwert", "Wasser/Kennwert")
  Next intI

  Range("A4").FormulaR1C1 = _
      "=SUMPRODUCT((R18C1:R10000C1>=Einstellung!R4C2)*(R18C1:R10000C1<=Einstellung!R4C3)*(R[14]C[1]:R[9996]C[1]))"
  Range("A4").AutoFill Destination:=Range("A4:AO4"), Type:=xlFillDefault
  Range("A10").FormulaR1C1 = _
      "=SUMPRODUCT((R18C1:R10000C1>=Einstellung!R8C2)*(R18C1:R10000C1<=Einstellung!R8C3)*(R[8]C[1]:R[9990]C[1]))"
  Range("A10").AutoFill Destination:=Range("A10:AO10"), Type:=xlFillDefault
End Sub

Habe es leider nicht hinbekommen die Formel
=WENN('Datenblatt Layout'!A3="";"";'Datenblatt Layout'!A3) in mein obendstehnden VNB reinzubauen.

Vllt. ist diesbezüglich ein Vorschlag möglich.

Die Dateien bekommen wir in Form einer Excel Tabelle, dass dann pro Projekt einmal eingefügt werden muss.

Viele Grüße muunez94

[Wastl]

Hi,

Wenn du dich genau an meine Anleitung gehalten hättest, hättest du genau das hier bekommen:

Code:

=IF('Datenblatt Layout'!A3="","",'Datenblatt Layout'!A3)

Daraus muss man nur noch die Anführungszeichen anpassen, dann sieht es doch so aus:

Code:

activecell.Formula="=IF('Datenblatt Layout'!A3="""","""",'Datenblatt Layout'!A3)"

PS:
Du musst nicht immer dem ganzen Beitrag von mir zietieren
PPS:
Wie weit biste mit der 1-Tabellenblatt-Lösung?

[RPP63]

*Popcorn*
Zunächst will ich durch diesen Post dies nicht mutwillig meinem avisierten #50 annähern.

Ich verweise noch einmal auf mein #10 und #16

Und jetzt mal mal im Ernst!
Es geht um 300 Objekte aka Gebäude und man lässt jemanden daran, der von Tuten und Blasen gleich mal überhaupt keine Ahnung hat?
Wie viel Stunden Deiner gut bezahlten Arbeitszeit hast Du bereits in diesen Blödsinn investiert?

Dies hier ist keine Job-Börse, aber dennoch gehört ein Fachmann an diese "Datenbank".
Dumm nur: Der verlangt Geld dafür, dass eure Firma Mehrwert erwirtschaftet.

[munez94]

Hi,

es ist ein Projekt über mein Studium in verbindung eines Moduls. Bin Vollzeitstudent. Trotzdem danke für deinen Beitrag und die guten Tipps!

Gruß


PS: Wastl hat mir sehr geholfen, danke dafür!