Move Duplicates from Sheet1 to Sheet2

Hi,
I’m new to Macros and I was wondering if anyone could please help me with VBA code for the following:

I have one workbook with two sheets which I will call Sheet1 and Sheet2

In Sheet1 there is data with an in definitive end point (i.e. the data will be copied and some weeks the data might be 100 rows, other weeks 200 rows).

A simplistic example of the data might look something like this:
Reference Solicitor Name Postcode
A11111 Alfies XXX ZZZ
B22222 Simons YYY ZZZ
C22222 Freds ZZZ ZZZ
A11111 Fionas KKK LLL
D22222 Dericks MMM NNN
C22222 Lisas III JJJ

There may be duplicates/ triplicates in column A – Sheet1 (Reference). Column A will not necessarily be sorted A-Z.
There will be 16 columns in sheet1 (A to P)
What I need to do is:
a) Copy all column headers (row A) from Sheet 1 to Sheet 2
b) Search column A (Sheet1) for duplicate entries (Duplicate entries in other columns are not a concern)
c) Once duplicate entries are located in column A (Sheet1), cut and paste the full row from Sheet1 to Sheet2 in row 2 downwards (Hence removing both sets of duplicates from column A - Sheet1 and retaining the headers copied in stage a)
d) Sort column A in Sheet2 by A-Z
e) Ensure there are no gaps on Sheet1 where entries have been removed.
f) Sort column A in Sheet1 by A-Z
The result using the above example would show:

Sheet1
Reference Solicitor Name Postcode
B22222 Simons YYY ZZZ
D22222 Dericks MMM NNN

Sheet 2
Reference Solicitor Name Postcode
A11111 Alfies XXX ZZZ
A11111 Fionas KKK LLL
C22222 Freds ZZZ ZZZ
C22222 Lisas III JJJ

Any help would be greatly appreciated!

Try this one

Please Login or Register  to view this content.

Wow! Works perfectly! Thanks so much! Next stage is for me to try and break it down and teach myself!