Stop Macro bringing across duplicate data

[excel_learner2]

Hi all,

I've found some help on here in the past, so thought I would try my luck again.

I currently have a Workbook that is pulling data from one sheet to another based on the value of a specific row. I need it to read the value "Yes" in one of the columns and copy the entire row to the new sheet. I have acheived this, created a button to run the Macros and copy the data over. All seems fine, however if you click the button again, it brings the data over again.

I need something that will enable the Macros to check if the value in Column B is already present, then it will not copy across. Please see the current code below:

Please Login or Register  to view this content.

Just to reiterate that the above works for the initial purpose of what I wanted, I just need it so that every time the button is clicked to update the new tab (we will need to do this each day) it checks if the data is already present.

If an example work book is required I can try and sort this out, but the nature of my work is quite sensitive so ideally I would not do this.

Hoping somebody is able to help! Thanks

[TMS]

If an example work book is required I can try and sort this out, but the nature of my work is quite sensitive so ideally I would not do this.

Yep, do that. It's not easy to work out a solution without being able to test it. I don't understand why, for example, you do this:

Please Login or Register  to view this content.

[ranman256]

import the data to a 'temp' sheet.
run REMOVE DUPLICATES,
then copy that set to the final sheet.

[TMS]

I should also have said, Welcome to the forum BUT ...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[excel_learner2]

Thanks TMS, I have used the code tag around the code now.

I will have to do it in the morning, but I will upload a sheet with some temporary data in tomorrow morning so things are a bit easier to explain.

Thanks again

[excel_learner2]

Hi,

I have a sample document that I have removed the majority of information from, I have left a few sample records on the sheet but removed company names and contact details.

In column C, "Ref. document number", this is what will be needed to be used to identify whether the data is already on the spreadsheet or not.

The workbook has been built up over time using various bits and pieces of code, so some of this may look messy. I know there will be risks of this corrupting the longer we use/more data is compiled to the book, however this is only a temporary measure so not a real long term concern. There will be similar code to what I need elsewhere in the spreadsheet but I am struggling to get my head around how to apply this to what I need below.

Just a summary of the assistance I require:

I want the sheet to copy a record to the "Uncollectables" tab when the word "Yes" is in the column "Uncollectable?" (R). The intention is to update this column throughout the day, and once the days work is completed, click the button top left which will run the code and copy the appropriate records to the next tab. The current problem is that whenever I press that button, it will copy all records regardless is they are already in the "Uncollectables" tab.

[TMS]

Well, I guess you can check whether or not the ID has already been copied across, but that would require you to loop through all the records, check them individually (I'd use COUNTIF). Alternatively, you could add a column, say headed "posted", and add a flag to all the records when they are posted. Then you would filter for unposted records; if there are any, copy them.

[jindon]

excel_learner2,

Why not change "Yes" to something like "Done" after you copy the row?

BTW, AutoFilter would suite in this situation.