Results 1 to 3 of 3

Thread: Copying data between worksheets

  1. #1

    Copying data between worksheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    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+
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    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!




    Quote Originally Posted by Ken Puls View Post
    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+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •