Copying data between worksheets

exceluseralpha

New member
Joined
Nov 9, 2012
Messages
2
Reaction score
0
Points
0
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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+
 

exceluseralpha

New member
Joined
Nov 9, 2012
Messages
2
Reaction score
0
Points
0
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!




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+
 
Top