Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How can I stop duplication when running a macro?

Posted on 2012-09-09
9
Medium Priority
?
467 Views
Last Modified: 2012-09-15
I have a macro that is working well except when run it for the third time it duplicates all the data that is to be copied and pasted. The duplication have been shown in red in the attached file. Also I have noticed the formating in the column with "No Month" entry is not the same as the first copy as shown for "September" month color "Green".

Can anyone look at the code and see if their is any obvious problem.

My second problem is how can I format the interior of the cell in the similiar code as shown below:

rngSrc.Copy
rngDst.Offset(LastRow, 3).PasteSpecial xlPasteValues
   With rngDst.Offset(LastRow, 3).Resize(rngSrc.Rows.Count)
    .HorizontalAlignment = xlRight
   End With

I would like to use the code below to the code above:

         With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 16777157
            .TintAndShade = 0
            .PatternTintAndShade = 0
         End With


Can anyone solve the two problems please.

THANKS
CopyData-Format.xlsx
0
Comment
Question by:user2073
  • 4
  • 3
7 Comments
 
LVL 36

Expert Comment

by:Norie
ID: 38380506
For the first part, it's hard to tell what the problem is without seeing the actual data in the EmployeeData sheet, I think that's the sheet involved anyway.

The second part - which cells do you want formatted?

The detstination range?
rngDst.Offset(LastRow, 3).PasteSpecial xlPasteValues
   With rngDst.Offset(LastRow, 3).Resize(rngSrc.Rows.Count)
    .HorizontalAlignment = xlRight

     With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 16777157
            .TintAndShade = 0
            .PatternTintAndShade = 0
         End With
End With

Open in new window

0
 

Author Comment

by:user2073
ID: 38380525
The code you supplied is working fine, THANKS.

As for the first portion of my problem. The problem is not the sheet as I have changed the order and also used different sheets the problem persists only on the third running of the macro.

I suspect the "rngSrc" row counting code.

Set rngSrc = Sheets("EmployeeData").Range("A5:A" & LastRow2)

I have "Rem" this code out the problem disappears totally but the macro only shows the first line of data not the full column.

I can't supply the entire working file as it is far too large.

But I appreciate your help.

THANKS
0
 
LVL 36

Accepted Solution

by:
Norie earned 1000 total points
ID: 38380620
I'm not saying there's a problem with the sheets.

It's just that it's hard to tell what the problem is without them, and the data they have.

You don't need to attach the whole workbook, create a smaller version of it with only the relevant worksheets and some representative data.

PS Did you try the code I posted for the formatting?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:user2073
ID: 38381494
I have tried the formatting Code that you provided and it is all working great, THANKS for that.
My First Poblem has not received any comment for the last 4 days. Can someone help me please.

Following on I have attached a greatly reduced and modified file with working macros. Could someone look at the file and see if they can find the cause of the problem.
The problem is when I run the macro on the third time after starting from the very first month eg. July 2011 and transfering data to the "SummaryOfEmployees" worksheet. The macro duplicates the data that is being copied using the raw data and does not reformat the duplicated cells. One thing I have notice by steping through the macro is it places the second lot of data under the correct data while using the line of code that reads:
     rngSrc.Copy
     rngDst.Offset(LastRow, 3).PasteSpecial xlPasteValues

If there is any issue please let me know.

I hope this helps, any assistance would be appreciated.

THANKS
TEST-Code-A.xlsm
0
 
LVL 36

Expert Comment

by:Norie
ID: 38393762
There is no worksheet Venice _ July 2011 as mentiioned in the instructions.

Can the problem be replicated with the other worksheets?
0
 

Author Comment

by:user2073
ID: 38396966
If you look at the 5th Comment titled "YOUR COMMENT". Attached to this comment is an attached file named TEST-Code-A.xlsm. This file has a complete working file with altered data for testing ONLY. It also contains the worksheet Venice_July 2011.

I hope this is sufficent for someone to help me.

THANKS
0
 

Author Closing Comment

by:user2073
ID: 38401250
Thanks for you help this question has been resolved by using a style of coding that I have not used previously. I appreciate this style and I would like to learn more this may mean I'll need to ask more in the future.

THANKS
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

572 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