Copying data between worksheets

I need VBA code to do the following in Excel please:

I basically want to copy a new row of data from WorkSheet1 into WorkSheet2 every time a copy button is clicked.

* Data inputted in WorkSheet1 - row A1:G1
* User clicks on a copy button displayed in WorkSheet1
* The row A1:G1 data is copied and entered as a new row in WorkSheet2 Row 1
* Data cleared from WorkSheet1 - row A1:G1
* Process is repeated but next time around the (new) WorkSheet1 - row A1:G1 data is copied and added to WorkSheet2 Row 2, then Row 3, Row 4 etc.


Can someone post step by step code to do this please for a basic Excel user, thanks.

Hey there and welcome to the forum.

The following code goes in a standard code module (see my signature for where to put it):


Once you've got the code in a module, then you can create a button and assign the code. Don't know which version of Excel you're using though... the steps are different between Excel 2003 and 2007+

That code worked, thanks a milion!

That code worked perfectly thanks a million!

Added the code to a cell-click change and it is working like a dream, thanks again!

Ken Puls said:

Hey there and welcome to the forum.

The following code goes in a standard code module (see my signature for where to put it):

Code:

Sub CopyData()
    Dim wsTarget As Worksheet
    Dim rngToCopy As Range
    
    'Set your worksheets here
    Set rngToCopy = Worksheets("Sheet1").Range("A1:G1")
    Set wsTarget = Worksheets("Sheet2")
    
    'Copy the range
    rngToCopy.Copy
    
    'Paste it to next row on target worksheet
    With wsTarget
        .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
    End With
    
    'Clear original range
    rngToCopy.ClearContents
End Sub

Once you've got the code in a module, then you can create a button and assign the code. Don't know which version of Excel you're using though... the steps are different between Excel 2003 and 2007+

Click to expand...