Copy data from a range and paste it in the next available row in another sheet

[brendalpzm]

I have a range ("A31:AB31") in the "Input incidents" sheet, so I need to paste it (only values, no formulas) at the next available row in a data base ("Incidents_Accu"),

the columns are also A:AB in the data base, the information should be accumulated in the data base by clicking on an ActiveX Controls Button.

 

[Trebor76]

Hi brendalpzm,

Try this:

Option Explicit
Sub Macro1()

    Dim lngPasteRow As Long
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook
        On Error Resume Next 'Stop the following line producing an error if there's no data on the tab
            lngPasteRow = .Sheets("Incidents_Accu").Range("A:AB").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        On Error GoTo 0
        lngPasteRow = IIf(lngPasteRow = 0, 2, lngPasteRow + 1) 'If the 'lngPasteRow' variable has no value set it 2 (change to suit) or else increment it by 1
        .Sheets("Incidents_Accu").Range("A" & lngPasteRow & ":AB" & lngPasteRow).Value = .Sheets("Input incidents").Range("A31:AB31").Value
    End With
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[brendalpzm]

Hi brendalpzm,

Try this:

Option Explicit
Sub Macro1()

    Dim lngPasteRow As Long
   
    Application.ScreenUpdating = False
   
    With ThisWorkbook
        On Error Resume Next 'Stop the following line producing an error if there's no data on the tab
            lngPasteRow = .Sheets("Incidents_Accu").Range("A:AB").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        On Error GoTo 0
        lngPasteRow = IIf(lngPasteRow = 0, 2, lngPasteRow + 1) 'If the 'lngPasteRow' variable has no value set it 2 (change to suit) or else increment it by 1
        .Sheets("Incidents_Accu").Range("A" & lngPasteRow & ":AB" & lngPasteRow).Value = .Sheets("Input incidents").Range("A31:AB31").Value
    End With
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

it works! thanks!

 

[Trebor76]

it works! thanks!

Thanks for letting us and you're welcome