Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1224to1228
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Zeilen auf mehreren Datenblättern einfügen
Kai
Hallo zusammen,
ich habe ein ein Tabellenblatt Verwaltung, darauf befindet sich ein CommanButton1, das ein userform1 aufruft.
Darauf befindet sich ComboBox1 und ComboBox2, sowie CommandButton1.
In ComboBox1 wird die Zeile ausgewählt, wo die neue Zeile eingefügt werden soll.
In ComboBox2 wird die Anzahl der Zeilen ausgewählt die eingefügt werden sollen.
Das ganze soll durchgeführt werden, wenn auf den CommandButton geklickt wird.
Mein Problem ist, das die Tabellenblätter 1 bis 14 alle gleich aussehen. Die eingefügten Zeilen, sollen deshalb auf allen Tabellenblättern eingefügt werden.
Leider bekomme ich es nur auf dem Tabellenblatt Verwaltung hin, kann mir jemand sagen was ich in den Code einfügen muss, damit es auf den Tabellenblättern 1-14 ausgeführt wird?
Private Sub CommandButton1_Click() ' fügt Zeilen ein und überträgt Formeln ' Variablen deklarieren Dim iZeile As Integer ' (erste) Zeile zum Einfügen Dim iAnZeile As Integer ' Anzahl der einzufügenden Zeilen Dim iAnz As Integer ' Schleifenzähler für einzufügende Zeilen Dim iLeSpalte As Integer ' letzte Tabellenspalte Dim arr() As String ' Übertragen der Formeln Dim iSpa As Integer ' Schleifenzähler Spalten ' Variablen dimensionieren iLeSpalte = ActiveCell.SpecialCells _ (xlLastCell).Column ' letzte Spalte ermitteln ReDim arr(iLeSpalte) ' Array auf Anzahl der Spalten dimensionieren iZeile = ComboBox1.Value ' erste Zeile zum Einfügen iAnZeile = ComboBox2.Value ' Anzahl der einzufügenden Zeilen ' Formeln in Array schreiben For iSpa = 1 To iLeSpalte ' laufe von erster bis zur letzten Spalte If Cells(iZeile, iSpa).HasFormula Then ' wenn Zelle eine Formel enthält, dann ... arr(iSpa) = Cells(iZeile, iSpa).Formula ' ... schreibe Formel in Variable Else ' oder ... arr(iSpa) = "" ' ... schreibe Leerstring End If ' Ende der Auswahl Next iSpa ' Schleifenzähler plus 1 ' Formeln in neue Zeile(n) schreiben For iAnz = 1 To iAnZeile ' laufe von 1 bis letzter Zeile Rows(iZeile).Insert Shift:=xlDown ' füge Zeile ein For iSpa = 1 To iLeSpalte ' laufe von erster bis zur letzten Spalte Cells(iZeile, iSpa) = arr(iSpa) ' schreibe Formel(n) in neue Zeile(n) Next iSpa ' Schleifenzähler plus 1 Next iAnz ' Schleifenzähler plus 1 UserForm1.Hide End Sub

Private Sub UserForm_Initialize()
' legt Startparameter fest
' Variablen deklarieren
Dim s As Integer                                ' Schleifenzähler
Dim iLeZeile As Integer                         ' letzte Zeile
Dim u As Integer                                ' Schleifenzähler
iLeZeile = ThisWorkbook.Sheets("Verwaltung").Range("A65536").End(xlUp).Row  ' Nummer der  _
letzten Zeile
For s = 2 To iLeZeile                           ' laufe von 2 bis letzte Zeile
With ComboBox1                              ' öffne Kombinationsfeld 1
.AddItem s                              ' schreibe Zeilenummer
End With                                    ' Ende schreiben
Next s                                          ' Schleifenzähler plus 1
For u = 1 To 10                                 ' laufe von 1 bis 10
With ComboBox2                              ' öffne Kombinationsfeld 2
.AddItem u                              ' schreibe Zahl
End With                                    ' Ende schreiben
Next u                                          ' Schleifenzähler plus 1
End Sub
Viele Grüße und vielen Dank
Kai
mit Schleife über die Tabellenblätter ...
01.08.2011 07:32:38
Matthias
Hallo Kai
anstatt
For iAnz = 1 To iAnZeile                        ' laufe von 1 bis letzter Zeile
Rows(iZeile).Insert Shift:=xlDown           ' füge Zeile ein
For iSpa = 1 To iLeSpalte                   ' laufe von erster bis zur letzten Spalte
Cells(iZeile, iSpa) = arr(iSpa)         ' schreibe Formel(n) in neue Zeile(n)
Next iSpa                                   ' Schleifenzähler plus 1
Next iAnz                                       ' Schleifenzähler plus 1
probierst mal so:
For x = 1 To Worksheets.Count                     'laufe von 1 bis letzte Tabelle
With Worksheets(x)
For iAnz = 1 To iAnZeile                        ' laufe von 1 bis letzter Zeile
.Rows(iZeile).Insert Shift:=xlDown          ' füge Zeile ein
For iSpa = 1 To iLeSpalte                   ' laufe von erster bis zur letzten Spalte
.Cells(iZeile, iSpa) = arr(iSpa)        ' schreibe Formel(n) in neue Zeile(n)
Next iSpa                                   ' Schleifenzähler plus 1
Next iAnz                                       ' Schleifenzähler plus 1
End With
Next
Hab ich Dich richtig verstanden ?
Gruß Matthias
Anzeige
AW: mit Schleife über die Tabellenblätter ...
01.08.2011 07:56:46
Kai
Hallo Mathias,
Danke hast mich genau richtig verstanden! Bis darauf, das es nur bis zu einem bestimmten Tabelleblatt laufen soll.
Aber das bekomme ich jetzt selber hin!
Vielen Dank dafür!
Gruß Kai
AW: mit Schleife über die Tabellenblätter ...
01.08.2011 13:16:22
Kai
Hallo zusammen,
brauche dringend Hilfe, das von mir oben beschriebene war leider noch nicht die Lösung des Problems und ich verzweifel.
Es klappt das die Zellen wie gewünscht eingefügt werden. Mein Problem:
In den Zellen stehen auch Bezüge, diese werden nicht übertragen. Ausserdem werden Summen in den Spalten gebildet. Wird nun eine Zeile eingefügt, wird die neue Zeile nicht in die Summe mit eingebunden.
Wie bekomme ich es hin, das es per vba so funktioniert, wie ich es sonst händisch mache.
Vorgehensweise:
Tabellenblätter 1-15 makieren Zeile wo eingefügt werden soll makieren. Zeilen einfügen und dann alle Formeln runterziehen und den Bereich bei der Summenbildung vergrößern. Insgesamt für 16 Summen :-(
Gut wäre es, wenn das automatisch passieren würde. Das Problem mit den Summen tritt ja auch nur auf, wenn am oberen Rand, oder unterem Rand eine Zeile eingefügt wird.
Gruß
Kai
Anzeige
AW: mit Schleife über die Tabellenblätter ...
01.08.2011 13:21:05
Kai
p.s. die Formate sollen auch übernommen werden. Momentan wird immer vor einer Zeile eingefügt, so das also der Zelle darunter das Format übernommen werden soll.
Testdatei leider selbst noch keine Lösung gefunden
02.08.2011 00:20:31
Kai

Die Datei https://www.herber.de/bbs/user/75974.xls wurde aus Datenschutzgründen gelöscht


Hallo zusammen,
ich habe nun mal eine Testdatei hochgeladen. Die Grünen-Bereiche zeigen die Bereiche, wo Zeilen eingefügt werden sollen.
Schwarze Schrift - Werte die immer in den Zellen stehen (Überschriften)
Rote Schrift - Zahlen die eingegeben werden
Blaue Schrift - Dort sind Formeln in den Zellen hinterlegt.
Was ich möchte:
Man soll aussuchen können hinter welcher Zeile eine weitere eingefügt werden soll.
Die Formatierung soll von der Zeile übernommen werden, die vor der eingefügten Zeile steht.
Die Blauen Formeln sollen fortgeführt werden, das heißt, als ob sie auf die neue Zeile heruntergezogen werden.
Die Zellen mit Blauer Schrift die Summen bilden, sollen in die Summenbildung die neuen Zellen mit einbeziehen.
Tabelle1 unterscheidet sich bei den Formeln von Tabelle2 und 3, da dort Spalte A und B von Tabelle1 übernommen werden.
Da der Tabellenaufbau der Blätter ähnlich ist, bzw. bis auf Blatt 1 identisch, soll das einfügen, für eine bestimmte Anzahl an Tabellenblätter durchgeführt werden.
Hoffe ich hab es jetzt ganz gut beschrieben :-) und mir kann jemand helfen.
Momentan ist es leider so, das nach dem einfügen alle Summenzellen angepasst werden müßen und der Hintergrund gelb und nicht wie es sein soll grün ist.
Bin für alle Ideen offen und freue mich über jede Antwort!
Viele Grüße
Kai
Anzeige
kurzer Test
02.08.2011 08:16:30
Matthias
Hallo Kai
wieso 1 to 3 ?
Wenn Du jetzt als einzufügende Zeilen in der Combobox eine 1 einstellst werden 3 Zeilen eingefügt
Sollte es nicht eher so sein?
        For iAnz = 1 To iAnZeile
'laufe von 1 bis Auswahl Combobox2[Anzahl einzufügender Zeilen]
.Rows(iZeile).Insert Shift:=xlDown     ' füge Zeile ein
For iSpa = 1 To iLeSpalte              ' laufe von erster bis zur letzten Spalte
.Cells(iZeile, iSpa) = arr(iSpa)   ' schreibe Formel(n) in neue Zeile(n)
Next iSpa                              ' Schleifenzähler plus 1
Next iAnz                                  ' Schleifenzähler plus 1


Ich habe jetzt mal in Zeile3 eine Zeile eingefügt
Tabelle1

 ABCDEFG
1 test123  
2a 10111211 
3     0 
4b 13141514alte Formel
5  23252721C4:=TEILERGEBNIS(9;C2:C3)

Formeln der Tabelle
ZelleFormel
F2=C2-D2+E2
F3=C3-D3+E3
F4=C4-D4+E4
C5=TEILERGEBNIS(9;C2:C4)
D5=TEILERGEBNIS(9;D2:D4)
E5=TEILERGEBNIS(9;E2:E4)
F5=C5+D5-E5


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Vor dem Einfügen stand in C4:=TEILERGEBNIS(9;C2:C3)
Jetzt in C5: siehe Tabellendarstellung. Die Formeln in Zeile5 wurden angepasst.
Übrigens: Die Berechnung steht in Deiner Datei auf manuell [Exceloptionen]
Weiter habe ich nicht getestet.
Gruß Matthias
Anzeige
AW: kurzer Test (neue Testdatei)
02.08.2011 09:34:12
Kai
Hallo Matthias,
du hast natürlich recht, es sollte To iAnZeile heißen und For x = 1 To 3. Der Fehler ist mir gestern beim erstellen der Testdatei unterlaufen.
Ich habe heute aber nochmal rumprobiert und deshalb eine neue Testdatei hochgeladen.
https://www.herber.de/bbs/user/75980.xls
Ich habe glaube ich viel zu kompliziert gedacht.
Ich habe das Makro nochmal geändert und es fügt jetzt wie gewünscht die Zeile ein.
Allerdings immer vor der ausgewählten Zeile.
Was ich jetzt noch bräuchte wäre 1. das es für Tabellenblatt 1-13 durchgeführt wird und 2. das man aussuchen kann, ob vor oder nach der ausgewählten Zeile eingefügt werden soll.
Folgender Code funktioniert leider nicht:
Private Sub CommandButton1_Click()
' fügt Zeilen ein und überträgt Formeln
' Variablen deklarieren
Dim iZeile As Integer                           ' Zeile zum Einfügen
Dim x As Integer
For x = 1 To 3                     'laufe von 1 bis Tabelle 3
With Worksheets(x)
iZeile = ComboBox1.Value                        ' erste Zeile zum Einfügen
ThisWorkbook.Sheets(x).Rows((iZeile) & ":" & (iZeile)).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
ThisWorkbook.Sheets(x).Range("D" & (iZeile)).ClearContents
ThisWorkbook.Sheets(x).Range("E" & (iZeile)).ClearContents
UserForm1.Hide
End Sub
Viele Grüße
Kai
p.s. falls jemand noch eine Idee hat, wie ich die auszuwählenden Zeilen so einschränke, das nur die Grünen Bereiche gewählt werden können, wäre das echt klasse, da es die Bedienung vereinfachen würde.
Anzeige
getestet & Vorschlag
02.08.2011 10:06:36
Matthias
Hallo Kai
Vielleicht als Ansatz
For s = 2 To iLeZeile ' laufe von 2 bis letzte Zeile
With ComboBox1 ' öffne Kombinationsfeld 1
If Cells(s, 1).Interior.ColorIndex = 4 Then .AddItem s ' schreibe Zeilenummer (wenn grün) _
i>
End With                                    ' Ende schreiben
Next s                                       ' Schleifenzähler plus 1
ComboBox1.MatchRequired = True               ' nur aus Liste wählen lassen
Gruß Matthias
AW: getestet & Vorschlag
02.08.2011 10:25:16
Kai
Halo Matthias,
danke für die Idee, die ist super! So funktionierts! Danke dafür.
Könntest du mir evtl. nochmal dabei helfen, das es auf mehrere Tabellenblätter angewendet wird?
So klappt es leider nicht:
Private Sub CommandButton1_Click()
' fügt Zeilen ein und überträgt Formeln
' Variablen deklarieren
Dim iZeile As Integer                           ' Zeile zum Einfügen
Dim x As Integer
For x = 1 To 3                     'laufe von 1 bis Tabelle 3
With Worksheets(x)
iZeile = ComboBox1.Value                        ' erste Zeile zum Einfügen
ThisWorkbook.Sheets(x).Rows((iZeile) & ":" & (iZeile)).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
ThisWorkbook.Sheets(x).Range("D" & (iZeile)).ClearContents
ThisWorkbook.Sheets(x).Range("E" & (iZeile)).ClearContents
UserForm1.Hide
End With
Next
End Sub
Gruß Kai
Anzeige
Code geändert ...
02.08.2011 10:50:43
Matthias
Hallo Kai
Private Sub CommandButton1_Click()
' fügt Zeilen ein und überträgt Formeln
' Variablen deklarieren
Dim iZeile As Integer                           ' Zeile zum Einfügen
Dim x As Integer
Application.ScreenUpdating = False 'Bildschirm-Aktualisierung aus
iZeile = ComboBox1.Value                        ' erste Zeile zum Einfügen
For x = 1 To 3                     'laufe von 1 bis Tabelle 3
ThisWorkbook.Worksheets(x).Activate
Rows((iZeile) & ":" & (iZeile)).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Range("D" & (iZeile)).ClearContents
Range("E" & (iZeile)).ClearContents
Application.CutCopyMode = False
Range("A1").Select  'obwohl ich ungern selektiere
Next
ThisWorkbook.Worksheets(1).Activate 'zurück auf Tabelle(Index=1)
UserForm1.Hide
End Sub
Gruß Matthias
Anzeige
AW: Code geändert ...
02.08.2011 13:45:15
Kai
Hallo Matthias,
vielen vielen Dank! Jetzt funktioniert es genau so wie ich es mir vorgestellt habe!
Viele Grüße
Kai
p.s.
Code des UserForm sieht nun so aus:
Option Explicit
Private Sub CommandButton1_Click()
' fügt Zeilen ein und überträgt Formeln
' Variablen deklarieren
Dim iZeile As Integer                           ' Zeile zum Einfügen
Dim x As Integer
Application.ScreenUpdating = False 'Bildschirm-Aktualisierung aus
iZeile = ComboBox1.Value                        ' erste Zeile zum Einfügen
For x = 1 To 15                     'laufe von 1 bis Tabelle 15
ThisWorkbook.Worksheets(x).Activate
Rows((iZeile) & ":" & (iZeile)).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Range("D" & (iZeile)).ClearContents
Range("E" & (iZeile)).ClearContents
Application.CutCopyMode = False
Range("A1").Select  'obwohl ich ungern selektiere
Next
ThisWorkbook.Worksheets(1).Activate 'zurück auf Tabelle(Index=1)
UserForm1.Hide
End Sub
Private Sub CommandButton2_Click()
' fügt Zeilen ein und überträgt Formeln
' Variablen deklarieren
Dim iZeile As Integer                           ' Zeile zum Einfügen
Dim x As Integer
Application.ScreenUpdating = False 'Bildschirm-Aktualisierung aus
iZeile = ComboBox1.Value                        ' erste Zeile zum Einfügen
For x = 1 To 15                     'laufe von 1 bis Tabelle 15
ThisWorkbook.Worksheets(x).Activate
Rows((iZeile) & ":" & (iZeile)).Select
Selection.Copy
Selection.Insert Shift:=xlUp
Range("D" & (iZeile)).ClearContents
Range("E" & (iZeile)).ClearContents
Application.CutCopyMode = False
Range("A1").Select  'obwohl ich ungern selektiere
Next
ThisWorkbook.Worksheets(1).Activate 'zurück auf Tabelle(Index=1)
UserForm1.Hide
End Sub
Private Sub UserForm_Initialize()
' legt Startparameter fest
' Variablen deklarieren
Dim s As Integer                                ' Schleifenzähler
Dim iLeZeile As Integer                         ' letzte Zeile
iLeZeile = ThisWorkbook.Sheets("Verwaltung").Range("A65536").End(xlUp).Row  ' Nummer der  _
letzten Zeile
For s = 2 To iLeZeile ' laufe von 2 bis letzte Zeile
With ComboBox1 ' öffne Kombinationsfeld 1
If Cells(s, 1).Interior.ColorIndex = 35 Then .AddItem s ' schreibe Zeilenummer (wenn grün)
End With                                    ' Ende schreiben
Next s                                       ' Schleifenzähler plus 1
ComboBox1.MatchRequired = True               ' nur aus Liste wählen lassen
End Sub

Anzeige

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige