Transfer data using a VBA button from 1 sheet to another RRS feed

  • Question

  •  I would like to transfer the data from a range of cells ranging from roughly 10 rows from 1 sheet to another. This is a form containing texts and also numbers.

    The objective being, once the form on sheet one is completed you press a VBA command button at which transfers the data on to another sheet which places the data in to approx 10 rows then it automatically moves to the next row down on sheet 2 ready for another source of data to be repeated. I would also like the data from sheet 1 to be cleared once its transferred to sheet 2.

    I appreciate any help.


    Wednesday, January 15, 2020 9:28 PM

All replies

  • Try this:

    Sub MoveData()
        Dim w1 As Worksheet
        Dim w2 As Worksheet
        Dim m As Long
        Dim n As Long
        Set w1 = Worksheets("Sheet1")
        Set w2 = Worksheets("Sheet2")
        m = w1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        On Error Resume Next
        n = w2.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        On Error GoTo 0
        w1.Range("A1:M" & m).Cut Destination:=w2.Range("A" & n + 1)
        Application.CutCopyMode = False
    End Sub

    Regards, Hans Vogelaar (

    Wednesday, January 15, 2020 10:11 PM