I have a macro that when is run for the third time the data is place twice.

Posted on 2012-09-14
Last Modified: 2012-09-15
This is the second time I have posted a request for help with this problem.
Basically, I have created a macro with the help from Expert Exchange.
The process is to copy certain data from one sheet to another, then add formulae to several cells in each row for analysis.
This macro runs according all the processes requested but when the macro is ran for the third time it creates a second copy of the data when starting with a clear sheet "SummaryOfEmployees" a sample is shown in the attached workbook on sheet "SummaryOfEmployees (2)" hear I have highlighted the duplication of data.
The problem i'm having is that I can not work out what is causing this problem only on the third time when the macro is ran.
Can anyone help by looking at the attached workbook which has two macros to carry out all processes.
If this is too difficult to resolve please let me know.

Any assistence would be appreciated.

Question by:user2073
    LVL 33

    Expert Comment

    Are you sure the problem is in the sub AddToSummaryOfEmployees?

    Could it not be in the sub RemDuplicates?
    LVL 33

    Accepted Solution

    Right, I found the problem.

    It was this:
    Set rngDst = Sheets("SummaryOfEmployees").Range("A4:A" & LastRow)

    Open in new window

    This was setting the destination to an increasing no of rows.

    The destination range should be the next empty cell.
    Set rngDst = Sheets("SummaryOfEmployees").Range("A" & LastRow+1)

    Open in new window

    I've attached a workbook with updated code, I did change a couple of other things.

    One of the main things I changed was the renaming of the active sheet at the beginning, instead of that the code now sets a reference, wsSrc, to the active sheet which is then used throughout the rest of the code.

    Author Closing Comment

    I'm sorry this question has taken so much of your time. But I appreciate the results the style of the your code has tought me some new code that does not appear if I record a macro in excel. All the code you have used has help me greatly.

    Thank you very much.
    LVL 33

    Expert Comment

    It's me who should be apologising for not following up.

    I don't know why I didn't, I download the file from the other question and this file but only had a quick look at the first one.

    One thing though was that I didn't seem to be getting any notificiation messages.

    Anway, at least it's resolved now.

    Out of interest, i this the code you ar going to use?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Suggested Solutions

    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now