Form Control Macro for copying the last row of data to the end

[TGirl66]

I need a macro button to copy the last row of filtered data and add it to the end. In this example, the the macro would copy row 10 (TLP Kenswick) and copy/paste it to Row11.

Filtered here it would take Conroe (row 8) and also pasting to the open row (11). Is this possible? Thank you so much!

 

[Domenic]

Okay, in that case, see if this does what you want...

Option Explicit

Sub CopyLastRow()

    Dim targetSheet As Worksheet
    Dim lastColumn As Long
    Dim lastRow As Long
    
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
    
    With targetSheet
        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    
    'clear contents for Row 26
    With targetSheet
        .Range(.Cells(26, 1), .Cells(26, lastColumn)).ClearContents
    End With
    
    lastRow = targetSheet.Range("A26").End(xlUp).Row
    
    'clear filter (optional)
    With targetSheet
        If .FilterMode Then .ShowAllData
    End With
    
    If lastRow >= 3 Then
        With targetSheet
            .Range(.Cells(lastRow, "A"), .Cells(lastRow, lastColumn)).Copy .Range("A26")
        End With
    Else
        MsgBox "No data found.", vbExclamation
    End If
    
End Sub

Hope this helps!

 

[TGirl66]

Okay, in that case, see if this does what you want...

Option Explicit

Sub CopyLastRow()

    Dim targetSheet As Worksheet
    Dim lastColumn As Long
    Dim lastRow As Long
   
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
   
    With targetSheet
        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
   
    'clear contents for Row 26
    With targetSheet
        .Range(.Cells(26, 1), .Cells(26, lastColumn)).ClearContents
    End With
   
    lastRow = targetSheet.Range("A26").End(xlUp).Row
   
    'clear filter (optional)
    With targetSheet
        If .FilterMode Then .ShowAllData
    End With
   
    If lastRow >= 3 Then
        With targetSheet
            .Range(.Cells(lastRow, "A"), .Cells(lastRow, lastColumn)).Copy .Range("A26")
        End With
    Else
        MsgBox "No data found.", vbExclamation
    End If
   
End Sub

Hope this helps!

You've been extremely helpful, I appreciate it. Happy Holidays.

 

[Domenic]

You'ver very welcome, and Happy Holidays to you too!

Cheers!