Das Clever-Excel-Forum.de - Treffen
... 14.-16. September 2018 im Allgäu ...

Tabellenblattvorlage per Button erweitern + Seitenzahlen
#1
Hallo Community,

ich verzweifle momentan an wahrscheinlich ziemlich simplen dingen, die mich bereits 3 Tage aufhalten. Nun da ich meine Grenzen erreicht habe, hoffe ich darauf, dass mir hier jemand helfen könnte.

Grundlegendes:
Ich habe ein Formular erstellt, welches einen Auftrag sowie den dazugehörigen Bericht beinhaltet. In diesem Formular soll so viel wie möglich automatisiert von "Auftrag" in den "Bericht" geführt werden. Das Formular besteht aus 4 "Auftragstabellenblättern" und 4 "Berichtstabellenblätter" (genannt: "1. Blatt_order"/"1.Blatt_report", usw.).

Meine Idee:
Das "3. Blatt_order" und "3. Blatt_report" dienen dazu, wesentliche Informationen also Kennwerte zu erfassen bzw. im Bericht auszuwerten. Im Auftrag soll alles manuell geschehen, d.h. der Auftraggeber gibt alles ein. Im Bericht allerdings soll das meiste automatisiert geschehen. Der Berichtersteller muss nur über ein Dropdown Menü eine Nr. auswählen und den dazugehörigen zweiten Kennwert eingeben, sodass ein S-Verweis den Rest automatisch ausfüllt. Hinzu kommen dann noch ein paar "lokale" Berechnungen im Tabellenblatt, welche allerdings nicht wesentlich sind.
Da solch ein Tabellenblatt stets eine Grenze erreicht, ist es notwendig, per einfachem Mausklick auf einem Button das Blatt zu erweitern. Bsp.: Der Auftraggeber füllt das 3. Blatt des Auftrags komplett aus und braucht noch 5 weitere Zeilen. Hierzu klickt er auf "Tabelle erweitern", welches das "3. Blatt_order (1)" erstellt und gleichzeitig auch noch das "3. Blatt_Report (1)", welches automatisch mit den anderen Auftragsblättern verknüpft wird, damit der Berichtersteller wieder aus einem Dropdown wählen kann.

Mein Problem:
Da meine VBA Kenntnisse nicht besonders gut sind, konnte ich Makros nur per "aufzeichnen" definieren. Hierzu habe ich nur eine Vorlage für ein Tabellenblatt einfügen lassen. Die Problematik hier ist aber, dass die Verknüpfungen mit den Auftragsblättern nicht automatisch geschehen. Stattdessen erhalte ich hauptsächlich den Fehler "#Bezug". Außerdem springt die Ansicht ziemlich unschön mehrmals hin und her, wobei auch Fragen eingeblendet werden, in denen die Aktualisierung der Bezüge gefordert wird.

Meine Frage:
Gibt es eine Möglichkeit, diese Blätter so zu verknüpfen, dass jedes erweiterte Blatt automatisch mit den Auftragsblättern verknüpft wird?

Meine 2. (banale) Frage:
Gibt es irgendeine Möglichkeit, die Seitenzahl per VBA in einer Zelle (W6) auf jeder Seite ausgeben zu lassen? (Seite x/y) Leider musste meine Kopfzeile für einen Aufbau verschwinden und ich habe auch keine Möglichkeit den Aufbau anders zu gestalten.

Vielen Dank im Voraus!

Beste Grüße
Green


Angehängte Dateien
.xlsm   Version 3.xlsm (Größe: 145,93 KB / Downloads: 7)
to top
#2
Hi,

(03.03.2016, 08:14)Green schrieb: Hierzu klickt er auf "Tabelle erweitern", welches das "3. Blatt_order (1)" erstellt und gleichzeitig auch noch das "3. Blatt_Report (1)", welches automatisch mit den anderen Auftragsblättern verknüpft wird, damit der Berichtersteller wieder aus einem Dropdown wählen kann.

Dazu das 3. Blatt (order und Report) kopieren und an den Eingabe-Stellen den Inhalt löschen, die Formeln und das Dropdown bleiben ja erhalten. Diesen Vorgang kannst Du ebenfalls aufzeichnen und dann das Makro hier zeigen, dann können wir es verallgemeinern und auf relevantes reduzieren.
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#3
(03.03.2016, 11:25)Rabe schrieb: Hi,

(03.03.2016, 08:14)Green schrieb: Hierzu klickt er auf "Tabelle erweitern", welches das "3. Blatt_order (1)" erstellt und gleichzeitig auch noch das "3. Blatt_Report (1)", welches automatisch mit den anderen Auftragsblättern verknüpft wird, damit der Berichtersteller wieder aus einem Dropdown wählen kann.

Dazu das 3. Blatt (order und Report) kopieren und an den Eingabe-Stellen den Inhalt löschen, die Formeln und das Dropdown bleiben ja erhalten. Diesen Vorgang kannst Du ebenfalls aufzeichnen und dann das Makro hier zeigen, dann können wir es verallgemeinern und auf relevantes reduzieren.

Vielen Dank für die schnelle Antwort. Ich habe das Szenario mal durchlaufen lassen. Hier mein Makro:


Code:
Sub Erweitern()
'
' Erweitern Makro
'
'
    Sheets("4. Blatt_order").Select
    Sheets.Add Type:= _
        "N:\TB\10_"xxxxxxxxxx"\25_"xxxxxxxxx"\"Nachname", "Vorname"\Auftragsformular + Bericht\Neue Version ab März 2016\Neuer Rechner (Problematik)\Vorläufige Vorlagen\Blatt 3 Order.xlsm"
    Sheets("4. Blatt_report").Select
    Sheets.Add Type:= _
        "N:\TB\10_"xxxxxxxxxx"\25_"xxxxxxxxx"\"Nachname", "Vorname"\Auftragsformular + Bericht\Neue Version ab März 2016\Neuer Rechner (Problematik)\Vorläufige Vorlagen\Blatt 3 Report.xlsm"
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("G16").Select
    Selection.FormulaArray = _
        "=IF(OR(RC[-3]="""",RC[1]="""",'3. Blatt_order'!RC[5]=""""),"""",VLOOKUP(RC[-3]:RC[-1]&RC[1]:RC[4],CHOOSE({1,2},'3. Blatt_order'!R16C9:R37C9&'3. Blatt_order'!R16C15:R37C15,'3. Blatt_order'!R16C12:R37C12),2,FALSE))"
    Selection.AutoFill Destination:=Range("G16:G37"), Type:=xlFillDefault
    Range("G16:G37").Select
    Range("L16").Select
    Selection.FormulaArray = _
        "=IF(OR(RC[-8]="""",RC[-4]="""",'3. Blatt_order'!RC[12]=""""),"""",VLOOKUP(RC[-8]:RC[-6]&RC[-4]:RC[-1],CHOOSE({1,2},'3. Blatt_order'!R16C9:R37C9&'3. Blatt_order'!R16C15:R37C15,'3. Blatt_order'!R16C24:R37C24),2,FALSE))"
    Selection.AutoFill Destination:=Range("L16:L37"), Type:=xlFillDefault
    Range("L16:L37").Select
    Range("M16").Select
    Selection.FormulaArray = _
        "=IF(OR(RC[-9]="""",RC[-5]="""",'3. Blatt_order'!RC[13]=""""),"""",VLOOKUP(RC[-9]:RC[-7]&RC[-5]:RC[-2],CHOOSE({1,2},'3. Blatt_order'!R16C9:R37C9&'3. Blatt_order'!R16C15:R37C15,'3. Blatt_order'!R16C26:R37C26),2,))"
    Selection.AutoFill Destination:=Range("M16:M37"), Type:=xlFillDefault
    Range("M16:M37").Select
    Range("R16").Select
    Selection.FormulaArray = _
        "=IF(OR(RC[-14]="""",RC[-10]="""",'3. Blatt_order'!RC[16]=""""),"""",VLOOKUP(RC[-14]:RC[-12]&RC[-10]:RC[-7],CHOOSE({1,2},'3. Blatt_order'!R16C9:R37C9&'3. Blatt_order'!R16C15:R37C15,'3. Blatt_order'!R16C34:R37C34),2,FALSE))"
    Selection.AutoFill Destination:=Range("R16:R37"), Type:=xlFillDefault
    Range("R16:R37").Select
    Range("S16").Select
    Selection.FormulaArray = _
        "=IF(OR(RC[-15]="""",RC[-11]="""",'3. Blatt_order'!RC[9]=""""),"""",VLOOKUP(RC[-15]:RC[-13]&RC[-11]:RC[-8],CHOOSE({1,2},'3. Blatt_order'!R16C9:R37C9&'3. Blatt_order'!R16C15:R37C15,'3. Blatt_order'!R16C28:R37C28),2,FALSE))"
    Selection.AutoFill Destination:=Range("S16:S37"), Type:=xlFillDefault
    Range("S16:S37").Select
    Range("T16").Select
    Selection.FormulaArray = _
        "=IF(OR(RC[-16]="""",RC[-12]="""",'3. Blatt_order'!RC[10]=""""),"""",VLOOKUP(RC[-16]:RC[-14]&RC[-12]:RC[-9],CHOOSE({1,2},'3. Blatt_order'!R16C9:R37C9&'3. Blatt_order'!R16C15:R37C15,'3. Blatt_order'!R16C30:R37C30),2,FALSE))"
    Selection.AutoFill Destination:=Range("T16:T37"), Type:=xlFillDefault
    Range("T16:T37").Select
    Range("AC16").Select
    Selection.FormulaArray = _
        "=IF(OR(RC[-25]="""",RC[-21]="""",'3. Blatt_order'!RC[3]=""""),"""",VLOOKUP(RC[-25]:RC[-23]&RC[-21]:RC[-18],CHOOSE({1,2},'3. Blatt_order'!R16C9:R37C9&'3. Blatt_order'!R16C15:R37C15,'3. Blatt_order'!R16C32:R37C32),2,FALSE))"
    Selection.AutoFill Destination:=Range("AC16:AC37"), Type:=xlFillDefault
    Range("AC16:AC37").Select
    Range("H9:M9").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('1. Blatt_order'!RC:R[1]C[5]="""",""Im 1. Blatt ausfüllen."",'1. Blatt_order'!RC:R[1]C[5])"
    Range("AA9:AH9").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('2. Blatt_order'!R[6]C[-19]="""",""Im 1. Blatt ausfüllen."",'2. Blatt_order'!R[6]C[-19])"
    Range("D16:F16").Select
    Sheets("3. Blatt_order (2)").Select
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("H9:M9").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('1. Blatt_order'!RC:R[1]C[5]="""",""Im 1. Blatt ausfüllen."",'1. Blatt_order'!RC:R[1]C[5])"
    Range("AI9:AR9").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('2. Blatt_order'!R[6]C[-27]="""",""Im 1. Blatt ausfüllen."",'2. Blatt_order'!R[6]C[-27])"
    Range("AI10").Select
    Sheets("3. Blatt_order").Select
End Sub


Das einzige was nun nicht stimmt sind die Dropdown Menüs, weil diese sich immer nur auf die erste Seite beziehen sowie die S-Verweise die auch weiterhin auf das erste Blatt bezogen sind und nicht auf alle (Alte + neu eingefügte). Dazu kommt noch eine Abfrage zu den Verknüpfungen welche man aktualisieren oder nicht aktualisieren kann.

Meine bedingte Formatierung einiger Felder wird ebenfalls zerschossen. Die Verweisen natürlich in der Ausgangsmappe auf andere Blätter. Als Vorlagen sind die Blätter allerdings einzeln abgespeichert, wodurch kein Verweis in der bedingten Formatierung auf andere Arbeitsmappen möglich ist. Oh man ich werde verrückt ...
Achja der Grund, weshalb ich das ganze als Vorlage einfüge und nicht einfach kopiere liegt darin, dass meine Zellenformatierung beim kopieren nicht übernommen wird. In der leeren Tabelle haben alle Zellen die gleiche Größe, in meinem Blatt sind diese leider unterschiedlich groß, weil es sich nicht vermeiden lies.

Vielen Dank und beste Grüße
Green
to top
#4
(03.03.2016, 12:53)Green schrieb: Vielen Dank für die schnelle Antwort. Ich habe das Szenario mal durchlaufen lassen. Hier mein Makro:
[...]
Das einzige was nun nicht stimmt sind die Dropdown Menüs, weil diese sich immer nur auf die erste Seite beziehen sowie die S-Verweise die auch weiterhin auf das erste Blatt bezogen sind und nicht auf alle (Alte + neu eingefügte). Dazu kommt noch eine Abfrage zu den Verknüpfungen welche man aktualisieren oder nicht aktualisieren kann.

ich hatte nichts davon geschrieben, ein neues Blatt einzufügen und mit den Formeln zu versehen, sondern das Blatt zu kopieren:
Zitat:Sub Blatt3_kopieren()
'
' Blatt3_kopieren Makro
'

'
   Sheets("3. Blatt_order").Copy Before:=Sheets(4)
   Range("A16:AN37,AS16:AT37").ClearContents
   Sheets("3. Blatt_report").Copy Before:=Sheets(9)
End Sub
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#5
(03.03.2016, 13:37)Rabe schrieb:
(03.03.2016, 12:53)Green schrieb: Vielen Dank für die schnelle Antwort. Ich habe das Szenario mal durchlaufen lassen. Hier mein Makro:
[...]
Das einzige was nun nicht stimmt sind die Dropdown Menüs, weil diese sich immer nur auf die erste Seite beziehen sowie die S-Verweise die auch weiterhin auf das erste Blatt bezogen sind und nicht auf alle (Alte + neu eingefügte). Dazu kommt noch eine Abfrage zu den Verknüpfungen welche man aktualisieren oder nicht aktualisieren kann.

ich hatte nichts davon geschrieben, ein neues Blatt einzufügen und mit den Formeln zu versehen, sondern das Blatt zu kopieren:
Zitat:Sub Blatt3_kopieren()
'
' Blatt3_kopieren Makro
'

'
   Sheets("3. Blatt_order").Copy Before:=Sheets(4)
   Range("A16:AN37,AS16:AT37").ClearContents
   Sheets("3. Blatt_report").Copy Before:=Sheets(9)
End Sub

Oh das tut mir leid. Da habe ich etwas missverstanden. Mit deinem Code wird jetzt 3. Blatt_order kopiert aber der report nicht. Es kommt die Fehlermeldung "400". Habe ich etwas falsch gemacht?^^

Fehler gefunden! Ich hatte die Zeilen abgeändert, wodurch deine Angaben nicht ganz stimmten.

Meine S-Verweise sehen natürlich nun wie folgt aus:
Code:
=WENN(ODER(D9="";H9="";'3. Blatt_order'!L9="");"";SVERWEIS(D9:F9&H9:K9;WAHL({1.2};'3. Blatt_order'!$I$9:$I$30&'3. Blatt_order'!$O$9:$O$30;'3. Blatt_order'!$L$9:$L$30);2;FALSCH))
Also Verweisen diese auf das erste Blatt des reports. Gibt es eine Möglichkeit, das so einzurichten, dass diese auf alle Blätter des Reports verweisen. Das gleiche dann auch für mein Dropdown?
Der Befehl des Dropdowns sieht so aus:
Code:
=BEREICH.VERSCHIEBEN('3. Blatt_order'!$I$8;22;0;-22)

Gruß
Green
to top
#6
Gibt es eine Möglichkeit, S-Verweise so zu definieren, dass diese alle Blätter die mit der Buchstabenfolge "3. Blatt_order" beginnen, durchsuchen?
Die Zellen, welche durchsucht werden bleiben dabei natürlich immer gleich.

Die selbe Angelegenheit dann auch noch für Dropdown Menüs also die Datenüberprüfung, wäre für mich notwendig.
to top
#7
Hallöchen,

Du müsstest für den SVERWEIS schon mit den genauen Blattnamen arbeiten. Eine mögliche Lösung findest Du da:
http://www.excelformeln.de/formeln.html?welcher=233

Für die Datenüberprüfung müsstest Du die Daten auf einem Blatt zusammenfassen. Die mag keine Listen von mehreren Blättern :-(
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
[-] Folgende(r) 1 Benutzer sagt Danke an schauan für diesen Beitrag:
  • Green
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste