+ Reply to Thread
Results 1 to 14 of 14

Copy Entire Row into New Sheet, remove duplicates

  1. #1
    Registered User
    Join Date
    05-07-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Copy Entire Row into New Sheet, remove duplicates

    Hello,

    I copied a macro that was posted on here that would allow me to click a command button. In Column B, there will be a list of categories each with a sheet of the same name. When you click the command button it will automatically copy and paste each row with that contains the specific category. For instance, for every row that contains the category "Alumni" it will copy the data into the "Alumni" sheet.

    My problem is that when I click the button a second time, it re-copies ALL the data, leading to duplicates in each worksheet. I'm going to be constantly updating the master sheet, so I don't want to have to go through each worksheet to delete duplicates every time I click the command button. The code I have is:

    Please Login or Register  to view this content.
    Last edited by aeh7b; 05-10-2011 at 09:42 AM.

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Copy Entire Row into New Sheet, remove duplicates

    Please read the forum rules, then amend your post using Code Tags. Once you have added the tags, we can begin resolving your problem.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  3. #3
    Registered User
    Join Date
    05-07-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Copy Entire Row into New Sheet, remove duplicates

    Fixed. Sorry about that.

  4. #4
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Copy Entire Row into New Sheet, remove duplicates

    Would it work to remove the data from "Front" sheet after copying it to your other sheets?

  5. #5
    Registered User
    Join Date
    05-07-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Copy Entire Row into New Sheet, remove duplicates

    I want to keep it in the front sheet too so that I have a record of everything. The workbook is a budget and the"Front" sheet will be a copy of all the expenditures and deposits for the account. Each worksheet will be specific categories where each expenditure falls under. The goal is to keep track of the overall budget as well as the sub-budgets within each category.

  6. #6
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Copy Entire Row into New Sheet, remove duplicates

    You could use code to search for each record you are going to add on its destination sheet, and not add it if it already exists. Let me know if you want to persue that.

    However, I think you should another sheet just for inserting records onto the "Front" sheet. If you use the front sheet to hold all your records, you can just use formulas to fill your various other sheets (like the Alumni one) and you won't need much vba at all. This would be easiest, and would also make the most sense from a data integrity standpoint because it is inefficient to store the same data in multiple locations.

  7. #7
    Registered User
    Join Date
    05-07-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Copy Entire Row into New Sheet, remove duplicates

    How difficult would it be to write the code that searches for each record on the destination sheet and doesn't add it if it already exists?

  8. #8
    Registered User
    Join Date
    05-07-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Copy Entire Row into New Sheet, remove duplicates

    Can you send me the code that would search for each record on the destination sheet and skip over or overwrite if it already exists? Thanks!

  9. #9
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Copy Entire Row into New Sheet, remove duplicates

    Here's a simple change to do that:

    Please Login or Register  to view this content.
    I'd like to reiterate that this way of setting up your spreadsheet is bad for data integrity, and can more easily lead to errors.

  10. #10
    Registered User
    Join Date
    05-07-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Copy Entire Row into New Sheet, remove duplicates

    Thank you! It's not for serious data, just a way to manage a budgets for a university group, so errors won't be a huge deal. Thanks again.

  11. #11
    Registered User
    Join Date
    05-07-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Copy Entire Row into New Sheet, remove duplicates

    Unfortunately, this doesn't seem to work. When I add new data, it repeats all the data, except the first one. So if I have 5 things listed for the Alumni category and then I add a 6th and click the command button, it'll add everything except the 1st thing on the list...How do I fix that?

  12. #12
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Copy Entire Row into New Sheet, remove duplicates

    Can you post a sample worksheet showing it doesn't work?

  13. #13
    Registered User
    Join Date
    05-07-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Copy Entire Row into New Sheet, remove duplicates

    I added the first four transactions to the "Spending" Sheet and clicked the button. It worked correctly to copy those to the "Alumni" sheet. When I added the 5th transaction and clicked the button, the first and fifth transaction were not copied to the sheet, but the 2nd-4th were duplicated. I'm not sure how to fix the problem. Before the additional code was added, whenever I clicked the button it would recopy all transactions, including the new ones.
    Attached Files Attached Files

  14. #14
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Copy Entire Row into New Sheet, remove duplicates

    I think I mixed up the row variables in the Evaluate line. Try this instead:

    Please Login or Register  to view this content.

+ 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