Preventing Data Douplication in Excel

[Shereen Shousha]

Hi all, i'm new to VBA so I do need some help. I'm trying to create some macros to help me accelerating my repeatable steps in my workbook. I have just got the way to correct the duplication when it occur but I hope you can help me to find a way to prevent it from happening. Here is the details to my macro. thanks

Sub Copy_Data()
'
' Copy_Data Macro
'
'
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\Said\Documents\Dubai VBA\VBA\Orders.xlsx" _
, Origin:=xlWindows
Range("A1").Select
Selection.End(xlDown).Select
'Range("A3267").Select
ActiveCell.Offset(1, 0).Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
7), Header:=xlNo

ActiveWindow.Close savechanges:=True

Application.CutCopyMode = False
Range("A1").Select
End Sub

 

[Joe4]

Welcome to the Board!

Please explain how you are defining "data duplication" and how it involves the macro you've posted.
Explain the whole process, and how you envision it working.

Are you trying to copy a whole range of data and want to prevent duplicate rows from appearing?
If so, this not an easy task (if you are copying whole ranges), and I would simply remove the duplicates afterwards (it is a whole lot easier to do it that way).
Is there some reason you don't want to do it that way?

 

[mole999]

you might try conditional format and set up the column that highlights a duplicate, catch them as they are entered, and works all the time

 

[Shereen Shousha]

Hi Joe, thank you for your help.
I'm creating several macros to combine in one macro to have an automated way of copying modified date of a certain range (example: a monthly date) from a monthly test file to an excel workbook representing the updated monthly report. So every month the data in the text file should be automatically uploaded into the monthly report by adding it to the end of the sheet month after another.


I do that through macro as I said, so the macro run to format the data, copy it and past it in the sheet. If the macro run more than once it can cause a data duplication. So as a corrective action I added the instructions as follows:

Selection.CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
7), Header:=xlNo

I wonder if there's another instruction that can be inserted instead to prevent it from happening originally. I imagine having an instruction checking if this specific month (lets say Nov-17) already exist? IF YES, then end process and may be pop up message saying Data already updated, and IF NO continue with the macro to update the data for this month.

this is the idea, but what is the proper code for it?
thanks

 

[Joe4]

I wonder if there's another instruction that can be inserted instead to prevent it from happening originally. I imagine having an instruction checking if this specific month (lets say Nov-17) already exist? IF YES, then end process and may be pop up message saying Data already updated, and IF NO continue with the macro to update the data for this month.

If you can clearly define the logic for checking for that, we should be able to write code to do that.
We will need specific details.

 

[Shereen Shousha]

Hi - please let me know what are the details you wish to have and i can share it with you
thx

 

[Joe4]

In looking at your macros & files, how can you, by looking at it, determine if there are duplicates before you load them?
Is there some sheet or file that exists or has records already?
If so, what is its location/name?
How can you match up your data to the correct name/sheet?

Remember, we know nothing about your project other than what you have told us here. If you only give us very general information, we can only give you general responses.
But if you give us detailed information, the greater the chance that we can give you code that will do what you want with minimal modification on your side.