VBA Autofill dynamisch gestalten

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: VBA Autofill dynamisch gestalten
von: Marcus
Geschrieben am: 03.09.2015 08:06:18

Hallo zusammen,
meine letzte Frage wurde leider noch nicht geklärt und ich habe noch immer keine Lösung im Netz gefunden, deshalb versuche ich hier nochmal mein Glück.
Folgendes Problem: Ich habe eine Excelliste als Tabelle formatiert. Diese Tabelle hat zwangsläufig jede Menge Formatierung und bedingte Formatierung (vereinfachtes Beispiel: $A11<>$A10). Ich möchte gern via Makro vor der letzten Zeile eine neue einfügen und die Formatierung der 3./4.-letzten Zeile bis zum Ende mit Autofill übernehmen. Denn wenn ich nur eine neue Zeile vor A11 einfüge, zerschießt es die Bedingte Formatierung zu: $A12<>$A10. Ich habe zwei Makros, die aber nur zum Teil das machen, was ich möchte.

Sub NeueZeile1()
Rows(Cells(Rows.Count, 1).End(xlUp).Row).Select
Selection.Insert Shift:=xlDown
Rows(Cells(Rows.Count, 1).End(xlUp).Row - 3).Select
Selection.AutoFill Destination:=Selection.Resize(2, Selection.Columns.Count), Type:= _
xlFillFormats
End Sub

Sub NeueZeile2()
Dim lngLast As Long
lngLast = Cells(Rows.Count, 1).End(xlUp).Row
Set SourceRange = Range("A8:J8")
Rows(Cells(Rows.Count, 1).End(xlUp).Row).Select
Selection.Insert Shift:=xlDown
SourceRange.AutoFill Destination:=Range("A8:J" & lngLast), Type:=xlFillFormats
End Sub
Im ersten Makro soll er die 3. letzte Zeile wählen und das Format nach unten ziehen – klappt aber nicht. Im zweiten Makro ist der Bereich leider nicht dynamisch. Die Anzahl der Spalten kann nämlich variieren und ich möchte keinesfalls immer bei A8 beginnen, sondern ein paar Zeilen vor Tabellenende.
Da die Liste als Tabelle formatiert ist, könnte man theoretisch am Ende einfach einen neuen Datensatz anfügen. Dabei schreibt es zwar die bedingte Formatierung fort, aber das normale Format, wie Rahmen, Farben etc. wird nicht übernommen. Es ist also ein Teufelskreis… Und meine VBA-Kenntnisse reichen nicht, um das Problem mit „Destination:=“ im ersten Makro zu lösen. Vielleicht geht’s aber auch viel einfacher. Über Ratschläge würde ich mich freuen.
MfG Marcus

Bild

Betrifft: AW: VBA Autofill dynamisch gestalten
von: fcs
Geschrieben am: 05.09.2015 07:30:08
Hallo Markus,
man kann die ganzen Aktionen unter VBA innerhalb des Tabellen-Objekts ausführen.
Ich hab eines meiner Makros um die von dir gewünschte Formatierung ergänzt.
Gruß
Franz

Sub Tabelle_neue_Zeile()
    Call fncTab_Objekt_neue_Zeile(ActiveSheet.ListObjects(1), AnzZeilen:=1, Zeilevorher:=2)
    'wenn es nur eine Tabelle gibt, sonst ggf. Nummer oder Name des Listobjektsapassen
End Sub
                                        
Public Function fncTab_Objekt_neue_Zeile(objList As ListObject, _
    Optional AnzZeilen As Long = 1, _
    Optional Zeilevorher As Long = -1) As Boolean
    ' AnzZeilen = Anzahl anzufügende Zeilen - Vorgabewert = 1
    ' Zeilevorher = Zeile vor letzter Zeile ab der Autofill-Formate angewendet werden soll
                'Werte >=0 (0 = letzte Zeile), bei Werten < 0 wird Format nicht kopiert
' An Tabelle (Listobjekt) neue_Zeile(n) anfügen und Autoformate ausfüllen
    
    Dim CopyRange As Range
    Dim Zeile_L As Long
    On Error GoTo Fehler
    
    fncTab_Objekt_neue_Zeile = True
    With objList
        Zeile_L = .DataBodyRange.Rows.Count
        'Tabelle vergrößern
        .Resize .Range.Resize(.Range.Rows.Count + AnzZeilen)
        If Zeilevorher < 0 Then Exit Function
        'Formate übertragen
        With .DataBodyRange
            If Zeile_L - Zeilevorher > 0 Then
                Set CopyRange = .Rows(Zeile_L - Zeilevorher)
                CopyRange.AutoFill CopyRange.Resize(Zeilevorher + AnzZeilen + 1), Type:= _
xlFillFormats
            Else
                Set CopyRange = .Rows(1)
                CopyRange.AutoFill CopyRange.Resize(Zeile_L + AnzZeilen), Type:=xlFillFormats
            End If
        End With
    End With
    Exit Function
Fehler:
    fncTab_Objekt_neue_Zeile = False
    MsgBox "Fehler-Nr.: " & Err.Number & vbLf & Err.Description
    Err.Clear
End Function


Bild

Betrifft: AW: VBA Autofill dynamisch gestalten
von: Marcus
Geschrieben am: 07.09.2015 09:25:03
Hallo Franz,
vielen vielen Dank. Das Makro macht genau das, was es machen soll. Die Funktion sieht zwar weitaus komplizierter aus, als ich gedacht hätte, aber es klappt.
MfG Marcus

 Bild

Beiträge aus den Excel-Beispielen zum Thema "VBA Autofill dynamisch gestalten"