none
VBA to copy data from Sheet 1 and Paste to Sheet2 RRS feed

  • Question

  • Hi, i am trying to write a Macro to copy data entry from Sheet1 to and to paste it to Sheet2 the last row of empty row. After that, clear the data entry from Sheet1.
    I am a beginner, please help me.
    Thank you very much.
    Wednesday, September 25, 2019 1:48 AM

Answers

  • Check and adjust this macro:

    Sub MyMacro1()
     
        Dim ws1 As Worksheet: Set ws1 = Sheet1
        Dim ws2 As Worksheet: Set ws2 = Sheet2
        
        Dim r1 As Range: Set r1 = ws1.UsedRange
        Dim r2 As Range: Set r2 = ws2.UsedRange
        
        Set r2 = r2.Cells(1, 1).Offset(r2.Rows.Count, 0)
        Set r2 = ws2.Range(r2, r2.Offset(r1.Rows.Count - 1, r1.Columns.Count - 1))
        
        ws2.Activate
        r2.Select
        r2.Value = r1.Value
        r1.Clear
     
    End Sub

     

    It assumes that Sheet1 and Sheet2 are present in your workbook.

    Wednesday, September 25, 2019 5:44 AM

All replies

  • Check and adjust this macro:

    Sub MyMacro1()
     
        Dim ws1 As Worksheet: Set ws1 = Sheet1
        Dim ws2 As Worksheet: Set ws2 = Sheet2
        
        Dim r1 As Range: Set r1 = ws1.UsedRange
        Dim r2 As Range: Set r2 = ws2.UsedRange
        
        Set r2 = r2.Cells(1, 1).Offset(r2.Rows.Count, 0)
        Set r2 = ws2.Range(r2, r2.Offset(r1.Rows.Count - 1, r1.Columns.Count - 1))
        
        ws2.Activate
        r2.Select
        r2.Value = r1.Value
        r1.Clear
     
    End Sub

     

    It assumes that Sheet1 and Sheet2 are present in your workbook.

    Wednesday, September 25, 2019 5:44 AM
  • Thank you very much for your kind assistance.

    Appreciate it.

    Jean

    Thursday, September 26, 2019 12:19 AM