[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-09-14
Medium Priority
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
  • 3
LVL 35

Expert Comment

ID: 38401180
Are you sure the problem is in the sub AddToSummaryOfEmployees?

Could it not be in the sub RemDuplicates?
LVL 35

Accepted Solution

Norie earned 2000 total points
ID: 38401200
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

ID: 38401243
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 35

Expert Comment

ID: 38401264
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?

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

834 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