Zeile einfügen, Formel erweitert sich nicht

Question

Hallöchen,

das einfügen einer neuen Zeile erfolgt über ein Makro welches ich im Netz gefunden habe.

Sub Zeilen_einfügen()

Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="passwort"
Selection.EntireRow.Insert
With Selection.EntireRow
.Offset(-1, 0).Resize(1).Copy
.PasteSpecial Paste:=xlPasteFormulas
End With
ActiveSheet.Protect Password:="passwort"
Application.ScreenUpdating = True
End Sub

Ich markiere hier also eine Zeile aktiviere das Makro, das Passwort wird entfernt, die Zeile kopiert, eine neue einfügt und das Passwort wird wieder gesetzt.

Soweit funktioniert es auch ausser wenn Formeln enthalten sind die auf ein anderes Tabellenblatt verweisen.

zb. wird aus:

=Ziel_Vorlage!AK29

das hier

=Ziel_Vorlage!AK29

hier eine Formel dessen Bezug im selben Tabellenblatt ist

=WENN(A213="";"#NV";"nicht zugeord.")

je nachdem welche Zeile man nun markiert wird daraus eben A212 oder A214. Bei obiger Formel funktioniert das aber nicht.

Könnte mir bitte jemand erklären wie ich obige Formel bearbeiten muss damit sie sich selbstständig erweitert?

Gruß
energun222

Answer 1

Hallo M.O.

das Ding ist echt geil geworden! Ich Dank Dir recht herzlich!

Gruß
energun222

Answer 2

Hallo,

ich habe hierzu doch noch eine Frage und habe dazu eine Beispielmappe mit samt deinem Makro hochgeladen.

https://www.dropbox.com/s/0wdwb3fesianjfh/ec.xlsm?dl=0

Der Bereich A1 bis G10 hat den Bereichsnamen zeileneceinfuegen

Der Bereich E1 bis G10 hat den Bereichsnamen ecloeschen

Dein Makro spricht diesen Bereichsnamen zeileneceinfuegen an.

Füge ich nun eine Zeile hinzu wird der Bereichsname und Bereich zeileneceinfuegen erweitert was auch super funktioniert.

Der Bereich ecloeschen wird natürlich auch mit erweitert jedoch nicht der Bereichsname, ich muss somit den Bereichsnamen ecloeschen händisch erweitern.

Könnte man das realisieren das dieser Bereichsname ecloeschen mitwandert/erweitert wird?

Gruß
energun222

Answer 3

Hallo energun,

mit dem folgendem Makro wird auch der Bereich ecloeschen entsprechend erweitert:

Sub Bereich_erweitern_hawadruck_ec()
Dim rng As Range
Dim lngZeilen As Long
Dim lngSpalten As Long

'Bildschirmaktualisierung ausschalten
Application.ScreenUpdating = False

'1. Range-Bereich festlegen
Set rng = Range("zeileneceinfuegen")

'letzte Zeile und Spalte des Bereichs ermitteln
lngZeilen = rng.Rows.Count
lngSpalten = rng.Columns.Count

'hinter letzten Zeile des Bereichs eine Zeile einfügen
Cells(rng.Cells(1).Row + lngZeilen, 1).EntireRow.Insert

'letzte Zeile des Bereichs kopieren und in neue Zeile einfügen
Range(Cells(rng.Cells(1).Row + lngZeilen - 1, rng.Cells(1).Column), Cells(rng.Cells(1).Row + lngZeilen - 1, rng.Cells(1).Column + lngSpalten - 1)).Copy Range(Cells(rng.Cells(1).Row + lngZeilen, rng.Cells(1).Column), Cells(rng.Cells(1).Row + lngZeilen, rng.Cells(1).Column + lngSpalten - 1))
Application.CutCopyMode = False

'Bereich um eine Zeile erweitern
rng.Resize(lngZeilen + 1, lngSpalten).Name = strName

'2. Range-Bereich festlegen
Set rng = Range("ecloeschen")

'letzte Zeile und Spalte des Bereichs ermitteln
lngZeilen = rng.Rows.Count
lngSpalten = rng.Columns.Count

'Bereich um eine Zeile erweitern
rng.Resize(lngZeilen + 1, lngSpalten).Name = strName

'Bildschirmaktualisierung einschalten
Application.ScreenUpdating = True

End Sub

Gruß

M.O.

Answer 4

Hallo M.O.

ich bekomme bei folgender Zeile

rng.Resize(lngZeilen + 1, lngSpalten).Name = strName

einen Laufzeitfehler 1004:Anwendungs- oder objektdefinierter Fehler


Gruß
energun222

Answer 5

Hallo,

sorry mein Fehler, hatte noch was geändert und nicht mehr getestet ;-(.
So klappt es aber:

Sub Bereich_erweitern_hawadruck_ec()
Dim rng As Range
Dim lngZeilen As Long
Dim lngSpalten As Long

'Bildschirmaktualisierung ausschalten
Application.ScreenUpdating = False

'1. Range-Bereich festlegen
Set rng = Range("zeileneceinfuegen")

'letzte Zeile und Spalte des Bereichs ermitteln
lngZeilen = rng.Rows.Count
lngSpalten = rng.Columns.Count

'hinter letzten Zeile des Bereichs eine Zeile einfügen
Cells(rng.Cells(1).Row + lngZeilen, 1).EntireRow.Insert

'letzte Zeile des Bereichs kopieren und in neue Zeile einfügen
Range(Cells(rng.Cells(1).Row + lngZeilen - 1, rng.Cells(1).Column), Cells(rng.Cells(1).Row + lngZeilen - 1, rng.Cells(1).Column + lngSpalten - 1)).Copy Range(Cells(rng.Cells(1).Row + lngZeilen, rng.Cells(1).Column), Cells(rng.Cells(1).Row + lngZeilen, rng.Cells(1).Column + lngSpalten - 1))
Application.CutCopyMode = False

'Bereich um eine Zeile erweitern
rng.Resize(lngZeilen + 1, lngSpalten).Name = "zeileneceinfuegen"

'2. Range-Bereich festlegen
Set rng = Range("ecloeschen")

'letzte Zeile und Spalte des Bereichs ermitteln
lngZeilen = rng.Rows.Count
lngSpalten = rng.Columns.Count

'Bereich um eine Zeile erweitern
rng.Resize(lngZeilen + 1, lngSpalten).Name = "ecloeschen"

'Bildschirmaktualisierung einschalten
Application.ScreenUpdating = True

End Sub

Gruß

M.O.

Answer 6

Hallo M.O.

sehr geil! Ich Dank Dir. :-)

Gruß
energun222

Answer 7

Hallo,

ich muss jetzt doch noch einmal nachfragen.

Makro funktioniert einwandfrei nur hab ich Probleme beim Aufruf

Makro 1 = Tabelle1
Makro 2 = Tabelle2

Ich befinde mich in Tabelle1 und in dieser rufe ich über einen Button das Makro 1 auf welches auch in Tabelle1 ausgeführt wird, gleich danach wird auch aus Tabelle1 das Makro 2 gestartet welches in Tabelle2 das Makro ausführen soll. Es führt es aber in Tabelle1 aus. Schreibe ich das Makro 2 direkt in die Tabelle2 und rufe es so auf

Application.Run "Tabelle2.Makro 2"

wird es ordnugsgemäß ausgeführt. Wenn ich das Makro 2 aber in ein normales Modul kopiere und es so aufrufe

Application.Run "Makro 2"

wird das Makro 2 nicht richtig ausgeführt oder es kommt Salat raus, in Tabelle2 passiert dann nichts.

Wie kann ich denn dem Makro 2 welches sich in Modul 2 befindet das es die ktion in Tabelle2 ausführen soll?

Gruß
energun222

Answer 8

Hallo nochemal :-) Sorry für Doppelpost

hab es jetzt auch mal so probiert

Application.Run Worksheets("Tabelle2").Makro2

Objekt unterstützt Methode nicht. :-(

Gruß
energun222

Answer 9

Hallo,

wenn du das Makro auch aus anderen Tabellen aufrufen willst, musst du das so ändern:

Sub Bereich_erweitern_hawadruck_ec()
Dim rng As Range
Dim lngZeilen As Long
Dim lngSpalten As Long

'Bildschirmaktualisierung ausschalten
Application.ScreenUpdating = False

With ThisWorkbook.Worksheets("Tabelle1")

'1. Range-Bereich festlegen
Set rng = .Range("zeileneceinfuegen")

'letzte Zeile und Spalte des Bereichs ermitteln
lngZeilen = rng.Rows.Count
lngSpalten = rng.Columns.Count

'hinter letzten Zeile des Bereichs eine Zeile einfügen
.Cells(rng.Cells(1).Row + lngZeilen, 1).EntireRow.Insert

'letzte Zeile des Bereichs kopieren und in neue Zeile einfügen
.Range(.Cells(rng.Cells(1).Row + lngZeilen - 1, rng.Cells(1).Column), .Cells(rng.Cells(1).Row + lngZeilen - 1, rng.Cells(1).Column + lngSpalten - 1)).Copy .Range(.Cells(rng.Cells(1).Row + lngZeilen, rng.Cells(1).Column), .Cells(rng.Cells(1).Row + lngZeilen, rng.Cells(1).Column + lngSpalten - 1))
Application.CutCopyMode = False

'Bereich um eine Zeile erweitern
rng.Resize(lngZeilen + 1, lngSpalten).Name = "zeileneceinfuegen"

'2. Range-Bereich festlegen
Set rng = .Range("ecloeschen")

'letzte Zeile und Spalte des Bereichs ermitteln
lngZeilen = rng.Rows.Count
lngSpalten = rng.Columns.Count

'Bereich um eine Zeile erweitern
rng.Resize(lngZeilen + 1, lngSpalten).Name = "ecloeschen"

End With

'Bildschirmaktualisierung einschalten
Application.ScreenUpdating = True

End Sub

Den Namen der Tabelle (hier "Tabelle1") in der With-Anweisung musst du natürlich auf deine Verhältnisse anpassen.

Gruß

M.O.

Answer 10

Hallo M.O.

ich will mal eben aufzeigen wie ich hier starte.

Tabelle4(HaWa_Druck )

ich klicke hier in eine Zelle und es öffnet sich eine Userform in welcher sich der Button EC befindet:

Code des Buttons:

Private Sub CommandButton2_Click()
Application.Run "privat_makro3"
Application.Run "Bereich_erweitern_hawa_ec"
End Sub

privat_makro3

hier wird einfach nur abgefragt ob man berechtigt ist, wenn ja dann wird das erste Makro ausgeführt danach das 2te

1. Makro soll in Tabelle4(HaWa_Druck ) ausgeführt werden was es auch tut da ich mich in dieser Tabelle befinde

Private Sub Bereich_erweitern_hawadruck_ec()
Dim rng As Range
Dim lngZeilen As Long
Dim lngSpalten As Long

'Bildschirmaktualisierung ausschalten
Application.ScreenUpdating = False

'Passwortschutz aufhaben
ActiveSheet.Unprotect Password:="passwort"

'1. Range-Bereich festlegen
Set rng = Range("zeileneceinfuegen")

'letzte Zeile und Spalte des Bereichs ermitteln
lngZeilen = rng.Rows.Count
lngSpalten = rng.Columns.Count

'hinter letzten Zeile des Bereichs eine Zeile einfügen
Cells(rng.Cells(1).Row + lngZeilen, 1).EntireRow.Insert

'letzte Zeile des Bereichs kopieren und in neue Zeile einfügen
Range(Cells(rng.Cells(1).Row + lngZeilen - 1, rng.Cells(1).Column), Cells(rng.Cells(1).Row + lngZeilen - 1, rng.Cells(1).Column + lngSpalten - 1)).Copy Range(Cells(rng.Cells(1).Row + lngZeilen, rng.Cells(1).Column), Cells(rng.Cells(1).Row + lngZeilen, rng.Cells(1).Column + lngSpalten - 1))
Application.CutCopyMode = False

'Bereich um eine Zeile erweitern
rng.Resize(lngZeilen + 1, lngSpalten).Name = "zeileneceinfuegen"

'2. Range-Bereich festlegen
Set rng = Range("ecloeschen")

'letzte Zeile und Spalte des Bereichs ermitteln
lngZeilen = rng.Rows.Count
lngSpalten = rng.Columns.Count

'Bereich um eine Zeile erweitern
rng.Resize(lngZeilen + 1, lngSpalten).Name = "ecloeschen"

'3. Range-Bereich festlegen
Set rng = Range("ecfehlloeschen")

'letzte Zeile und Spalte des Bereichs ermitteln
lngZeilen = rng.Rows.Count
lngSpalten = rng.Columns.Count

'Bereich um eine Zeile erweitern
rng.Resize(lngZeilen + 1, lngSpalten).Name = "ecfehlloeschen"

'Passwortschutz wieder einrichten
ActiveSheet.Protect Password:="passwort"

'Bildschirmaktualisierung einschalten
Application.ScreenUpdating = True

End Sub

2. Makro soll in Tabelle5(HaWa) ausgeführt werden tut es aber nicht, es wird weiterhin in Tabelle4 ausgeführt

Private Sub Bereich_erweitern_hawa_ec()
Dim rng As Range
Dim lngZeilen As Long
Dim lngSpalten As Long
Dim strName As String

'Bildschirmaktualisierung ausschalten
Application.ScreenUpdating = False

With ThisWorkbook.Worksheets("HaWa")

'Passwortschutz aufhaben
ActiveSheet.Unprotect Password:="passwort"

'Name des Bereichs - anpassen
strName = "zeilenhawaeinfuegen"

'Range-Bereich festlegen
Set rng = Range(strName)

'letzte Zeile und Spalte des Bereichs ermitteln
lngZeilen = rng.Rows.Count
lngSpalten = rng.Columns.Count

'hinter letzten Zeile des Bereichs eine Zeile einfügen
Cells(rng.Cells(1).Row + lngZeilen, 1).EntireRow.Insert

'letzte Zeile des Bereichs kopieren und in neue Zeile einfügen
Range(Cells(rng.Cells(1).Row + lngZeilen - 1, rng.Cells(1).Column), Cells(rng.Cells(1).Row + lngZeilen - 1, rng.Cells(1).Column + lngSpalten - 1)).Copy

With Range(Cells(rng.Cells(1).Row + lngZeilen, rng.Cells(1).Column), Cells(rng.Cells(1).Row + lngZeilen, rng.Cells(1).Column + lngSpalten - 1))
.PasteSpecial Paste:=xlPasteFormulas
End With
Application.CutCopyMode = False

'Bereich um eine Zeile erweitern
rng.Resize(lngZeilen + 1, lngSpalten).Name = strName

'Passwortschutz wieder einrichten
ActiveSheet.Protect Password:="passwort"

End With

'Bildschirmaktualisierung einschalten
Application.ScreenUpdating = True

End Sub

Falls wir es hier nicht hinbekommen sollten würde ich dir die Mappe gerne wenn du damit einverstanden bist privat auf E-Mail senden da ich diese nicht gerne öffnetlich zum Download anbieten möchte da sie doch sehr umfangreich ist. Den Fehler oder Lösung kann man dann natürlich hier posten versteht sich.

Gruß
energun222