+ Reply to Thread
Results 1 to 8 of 8

Macro to Copy Values, Insert Row, Paste in Another, Delete Original Data

  1. #1
    Registered User
    Join Date
    03-06-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    5

    Macro to Copy Values, Insert Row, Paste in Another, Delete Original Data

    Hello,

    I am new to the forum. What I am trying to accomplish is to have a data entry form on a Sheet called "Data Copy" and then transfer that data that is collected into a table on the Sheet "Data". Each time a line of data is entered on the Data Copy sheet, the user will push a button to activate the macro which will transfer the line of data to the Data sheet, building a table of data, save the data/file and then clear the data on the Data Copy sheet.

    I am trying to create a macro that will do the following:

    1. From sheet "Data Copy", copy the values in a row of data from A2:N2. Some of the data is selected from drop down lists and I only want to copy the values.
    2. Paste or insert the values A2:N2 from "Data Copy" to another sheet called "Data" after inserting a blank row or inserting the values at the top in Cells A2:N2 (row 1 will contain headers)
    3. Save the data
    4. Clear or delete the data/values in the "Data Copy" sheet A2:N2

    Your help is much appreciated in advance.

    Cheers!

    Jim

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Macro to Copy Values, Insert Row, Paste in Another, Delete Original Data

    try this macro:

    Please Login or Register  to view this content.
    Last edited by DGagnon; 03-06-2012 at 07:13 PM. Reason: Edited code to paste values
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Registered User
    Join Date
    03-06-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro to Copy Values, Insert Row, Paste in Another, Delete Original Data

    Wow! that was a quick response. Tthank you!

    It works... almost. copies and clears the data over the other sheet but it copies everything, the formatting, and drop down data validation box as well. I would like to only copy over the value in the cell and no formulas/formatting. I've uploaded an example of what I'm trying to do.
    Attached Files Attached Files

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Macro to Copy Values, Insert Row, Paste in Another, Delete Original Data

    i recorded this using the macro recorder, but it should work for you:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-06-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro to Copy Values, Insert Row, Paste in Another, Delete Original Data

    Brilliant! No drop down list boxes copied (formatting was copied but no big deal, I can live with that)!

    Thanks for the help!

    Jim

  6. #6
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Macro to Copy Values, Insert Row, Paste in Another, Delete Original Data

    it shouldnt have copied any formating, after inserting the new row (row 2) it takes what ever formating is in row 3 and format paste's that into row 2, then takes the values from Data Copy sheet and paste's those values in. as long as row 2 has no formating before you run the macro, it should be good.

  7. #7
    Registered User
    Join Date
    03-06-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Macro to Copy Values, Insert Row, Paste in Another, Delete Original Data

    Correct, in my version/file, I didn't clear the formatting from my previous version/attempts and it copied as you said above. I cleared the formatting for row 3 and it copied cleanly.

    Thanks again!

    Cheers,

    Jim

  8. #8
    Registered User
    Join Date
    01-19-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Macro to Copy Values, Insert Row, Paste in Another, Delete Original Data

    Is it possible to use this Code with AdvancedFilter ?
    So it only cuts and pastes rows with a filter?
    I have been trying but I just cant combinate them..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1