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,

wenn ich deinen Code teste, dann wird auch beim Verweis auf ein anderes Arbeitsblatt die Formel entsprechend fortgeführt.

Bist du sicher, dass der Verweis in deiner Tabelle
=Ziel_Vorlage!AK29
lautet und nicht
=Ziel_Vorlage!$AK$29

Gruß

M.O.

Answer 2

Hallo,

du hast natürlich Recht die Formel wird nach unten fortgeführt aber leider nur wenn ich eine leere Zeile markiere. Markiere ich eine Zelle mitten drin wo schon Formeln vorhanden sind wird diese kopiert und unten drunter gesetzt.. Die 2. Formel wird nach unten korrigiert die erste aber nicht.

zb. sieht es so aus:

=Ziel_Vorlage!AK29
=Ziel_Vorlage!AK30
=Ziel_Vorlage!AK31

Markiere ich nun die Zeile mit:

=Ziel_Vorlage!AK29

und füge eine Zeile ein, schaut es dann so aus:

=Ziel_Vorlage!AK29
=Ziel_Vorlage!AK29
=Ziel_Vorlage!AK30
=Ziel_Vorlage!AK31

anstatt:

=Ziel_Vorlage!AK29
=Ziel_Vorlage!AK30
=Ziel_Vorlage!AK31
=Ziel_Vorlage!AK32

Ich habe leider keine freie Zeile da dieser Bereich zwischen 2 anderen liegt und ich das auch nicht ändern kann.

Gruß
energun222

Answer 3

Hallo,

natürlich wird die erste Formel nicht korrigiert, das macht Excel ja auch nicht. Dein Makro kopiert ja nur die vorherige Zeile in die neue Zeile. Und daher dürfte sich auch nicht die Formel, die sich nur auf dein aktuelles Tabellenblatt bezieht, automatisch nach unten korrigieren (bei mir tut sie es jedenfalls nicht).

Willst du das so haben, so musst du das anders lösen. Mit dem folgenden Makro wird eine Zeile eingefügt und die Formeln in Zeile 2 werden in den Spalten A und B nach unten aufgefüllt:

Sub Zeilen_einfügen()

Dim lngLetzte As Long

'Makro nur ausführen, wenn ab Zeile 3 eine neue Zeile eingefügt wird
If ActiveCell.Row < 3 Then
MsgBox "Es darf erst ab Zeile 3 eine neue Zeile eingefügt werden!", 16, "Abbruch"
Exit Sub
End If

'letzte Zeile in Spalte A ermitteln
lngLetzte = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

ActiveSheet.Unprotect Password:="passwort"
Selection.EntireRow.Insert

'Spalten A und B ab Zeile 2 bis Ende herunterkopieren
Range(Cells(2, 1), Cells(lngLetzte, 2)).FillDown

ActiveSheet.Protect Password:="passwort"

Application.ScreenUpdating = True

End Sub

Das Makro musst du natürlich auf deine Verhältnisse anpassen.

Gruß

M.O.

Answer 4

Hallo,

schon mal danke für dein Post!

Mir ist da noch was in den Sinn gekommen. Folgendes Szenario. Ich kann ja einer Zelle, einer Zeile oder einen ganzen Bereich mit einem Namen definieren, so mache ich es jedenfalls wenn ich mit einem Button schnell durch die Tabelle springen will.

Wie würde es denn aussehen wenn ich dem Makro sagen will: suche letzte Zeile vom definierten Bereich zb. Bereich1 und füge darunter eine neue Zeile ein und eben mit den ganzen Formeln übernehmen?

Gruß
energun222

Answer 5

Hallo,

das folgende Makro prüft zuerst, ob die aktive Zelle einem Bereich zugeordnet ist. Ist dies der Fall, so wird eine neue Zeile eingefügt und die Formeln aus der letzten Zeile des Bereichs in die neue Zeile eingefügt. Dann wird der Bereich um die neue Zeile erweitert. Ist die aktive Zelle keinem Bereich zugeordnet, so erfolgt eine Fehlermeldung. Es erfolgt auch nur eine Kopie des Bereichs, nicht der ganzen Zeile:

Sub erweitern()
Dim rng As Range
Dim lngZeilen As Long
Dim lngSpalten As Long
Dim nm As Name
Dim strName As String
Dim bgefunden As Boolean

'Bildschirmaktualisierung ausschalten
Application.ScreenUpdating = False

'Prüfen, ob aktuelle Zelle einem Bereich zugeordnet ist
For Each nm In ActiveWorkbook.Names
If Not Application.Intersect(ActiveCell, Range(nm.RefersToRange.Address)) Is Nothing Then
'Bereich in Range schreiben
Set rng = Range(nm.Name)
strName = nm.Name
bgefunden = True
Exit For
End If
Next

'Fehlermeldung, dass Zelle keinem Bereich zugeordnet ist
If bgefunden = False Then
MsgBox "Die aktuelle Zelle ist keinem Bereich zugeordnet!", 16, "Abbruch"
Exit Sub
End If

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

'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"

Application.ScreenUpdating = True

End Sub

Gruß

M.O.

Answer 6

Hallo,

das ist der Hammer. :-)

Ich trau mich garnicht zu fragen! Könnte man vielleicht direkt einen Bereich ansprechen ohne diesen zu markieren?

Gruß
energun222

Answer 7

Hallo,

natürlich geht das auch. Das ist sogar noch einfacher ;-):

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

'Bildschirmaktualisierung ausschalten
Application.ScreenUpdating = False

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

'Name des Bereichs - anpassen
strName = "Test"

'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"

'Bildschirmaktualisierung einschalten
Application.ScreenUpdating = True

End Sub

Gruß

M.O.

Answer 8

Hallo,

funktioniert einwandfrei. Hab da aber noch ein paar Fragen dazu.

1. wenn ich in diesem Tabellenregister ich nenne es mal Register1 eine Zeile hinzufüge übernimmt er mir die Formeln und auch die Farbgebung aber nicht die Rahmenlinien (siehe Link)

https://www.dropbox.com/s/xhf5fa9cszps5nn/2016-10-19%2012_12_37-Microsoft%20Excel%20-%20MHD_Liste_zum_LT_2016.10.20.xlsm.png?dl=0

Woran könnte das denn liegen?

2. ich habe ein weiteres Register hier Register2, auch hierfür habe ich dein Makro mit passenden definierten Namen in ein weiteres Modul eingefügt. Führe ich es aus wird auch hier einwandfrei eine Zeile hinzugefügt

Register1=Dateneingabe=hawadruck
Register2=Summen aller Eingaben=hawa

Es muss also in beiden Registern (wichtig) erst Register1 und danach Register2 eine Zeile hinzugefügt werden und ich dachte ich rufe das 2. Makro einfach am Ende von Makro 1 auf. Es werden beide Makros in Register1 ausgeführt obwohl die Makros und auch die Bereichsnamen anderst benannt sind.

das erste Makro heißt: Bereich_erweitern_hawadruck

habe halt unten reingeschrieben

Call Bereich_erweitern_hawa

Ist das falsch geschrieben oder hab ich da einen Denkfehler?

Gruß
energun222

Answer 9

Hallo nochmal,

ich glaube ich habe das 2. Problem lösen können indem ich das 2. Makro in Register2 geschrieben habe und es aus dem ersten makro 2 aufrufe

Call Tabelle2.Bereich_erweitern_hawa

ist das richtig so?

Bleibt vielleicht nur noch die erste Frage. :-)

Gruß
energun222

Answer 10

Hallo,

die Makros sollten in einem Standard-Modul stehen: KLICK!
Dann klappt auch auch der Aufruf mit

Call Bereich_erweitern_hawa

Da in deinem ursprünglichen Makro nur die Formeln kopiert werden sollen, hatte ich das auch so übernommen.
Wenn du auch die Rahmen kopiert haben möchtest, dann ändere die Zeilen

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

in

'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))

Gruß

M.O.