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]

Try the following code, which needs to be placed in a regular module (Visual Basic Editor [Alt+F11] >> Insert >> Module). Note that it assumes that the workbook running the code contains the data, and that the data is contained on Sheet1. Change the sheet name accordingly.

Option Explicit

Sub CopyLastRowFilter()

    Dim targetSheet As Worksheet
    Dim dataFilterRange As Range
    Dim firstColumn As Long
    Dim nextRow As Long
 
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1") 'change the worksheet name accordingly
 
    If targetSheet.FilterMode = False Then
        MsgBox "No filter applied.", vbExclamation
        Exit Sub
    End If
 
    With targetSheet.AutoFilter.Range
 
        On Error Resume Next
        Set dataFilterRange = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        If dataFilterRange Is Nothing Then
            MsgBox "No records found.", vbExclamation
            Exit Sub
        End If
        On Error GoTo 0
     
        firstColumn = .Cells(1, 1).Column
     
        nextRow = .Cells(.Rows.Count, 1).Row + 1
     
    End With
 
    With dataFilterRange
        With .Areas(.Areas.Count)
            .Rows(.Rows.Count).Copy targetSheet.Cells(nextRow, firstColumn)
        End With
    End With
 
End Sub

Hope this helps!

 

[TGirl66]

Try the following code, which needs to be placed in a regular module (Visual Basic Editor [Alt+F11] >> Insert >> Module). Note that it assumes that the workbook running the code contains the data, and that the data is contained on Sheet1. Change the sheet name accordingly.

Option Explicit

Sub CopyLastRowFilter()

    Dim targetSheet As Worksheet
    Dim dataFilterRange As Range
    Dim firstColumn As Long
    Dim nextRow As Long
 
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1") 'change the worksheet name accordingly
 
    If targetSheet.FilterMode = False Then
        MsgBox "No filter applied.", vbExclamation
        Exit Sub
    End If
 
    With targetSheet.AutoFilter.Range
 
        On Error Resume Next
        Set dataFilterRange = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        If dataFilterRange Is Nothing Then
            MsgBox "No records found.", vbExclamation
            Exit Sub
        End If
        On Error GoTo 0
    
        firstColumn = .Cells(1, 1).Column
    
        nextRow = .Cells(.Rows.Count, 1).Row + 1
    
    End With
 
    With dataFilterRange
        With .Areas(.Areas.Count)
            .Rows(.Rows.Count).Copy targetSheet.Cells(nextRow, firstColumn)
        End With
    End With
 
End Sub

Hope this helps!

Hi Domenic, thank you for this information. Seems like we are close. once I enter this in vba can I enter a control botton and assign the macro? When I did this I recieved this error. Any thoughts?

 

[Domenic]

You are receiving that message because the data in the target worksheet (in my example it's Sheet1) hasn't been filtered. If you first filter the data, and then run the code, it should copy the last filtered data to the next available row, which is what I understood you wanted. Is this not the case? Or did you want to copy/paste regardless of whether the data is filtered?

 

[TGirl66]

Does it matter if it’s filtered by a slicer? Thanks

 

[TGirl66]

You are receiving that message because the data in the target worksheet (in my example it's Sheet1) hasn't been filtered. If you first filter the data, and then run the code, it should copy the last filtered data to the next available row, which is what I understood you wanted. Is this not the case? Or did you want to copy/paste regardless of whether the data is filtered?

Correct, I'd prefer to copy/paste regardless of whether the data is filtered. Thank you for your assistance.

 

[Domenic]

Okay, in that case, try the following code instead, which will copy/paste regardless of whether the data is filtered...

Option Explicit

Sub CopyLastRow()

    Dim targetSheet As Worksheet
    Dim lastColumn As Long
    Dim lastRow As Long
    Dim nextRow As Long
    Dim copyFromRange As Range
    
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
    
    With targetSheet
        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    If lastRow = 1 Then
        MsgBox "No records found.", vbExclamation
        Exit Sub
    End If
    
    With targetSheet
        Set copyFromRange = .Range(.Cells(lastRow, "A"), .Cells(lastRow, lastColumn))
    End With
    
    With targetSheet
        If .FilterMode Then .ShowAllData
    End With
    
    With targetSheet
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
    
    copyFromRange.Copy targetSheet.Cells(nextRow, "A")
    
End Sub

Hope this helps!

 

[TGirl66]

Okay, in that case, try the following code instead, which will copy/paste regardless of whether the data is filtered...

Option Explicit

Sub CopyLastRow()

    Dim targetSheet As Worksheet
    Dim lastColumn As Long
    Dim lastRow As Long
    Dim nextRow As Long
    Dim copyFromRange As Range
   
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
   
    With targetSheet
        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
   
    If lastRow = 1 Then
        MsgBox "No records found.", vbExclamation
        Exit Sub
    End If
   
    With targetSheet
        Set copyFromRange = .Range(.Cells(lastRow, "A"), .Cells(lastRow, lastColumn))
    End With
   
    With targetSheet
        If .FilterMode Then .ShowAllData
    End With
   
    With targetSheet
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
   
    copyFromRange.Copy targetSheet.Cells(nextRow, "A")
   
End Sub

Hope this helps!

Good morning, this does work, but because I have signature lines within the range it will copy those as being the last row of data. Is there a way to indicate in the code to copy data up to row 25? Thank you!

 

[Domenic]

So, if I understood you correctly, the last row to be copied will occur between Row 3 and Row 25, inclusive, with filtered and non-filtered data, correct?

If so, does the last row within that range always get copied to Row 26?

 

[TGirl66]

Yes, that's correct. Thank you, Domenic.