Macro - Copy, Paste and Remove Duplicates

[klloyd76]

Hi all,
I am EXTREMELY new to macros. I have a project I am working on and got to the point where I can filter, copy and paste the data from one sheet to another. I need to know how to remove duplicates on the pasted worksheet. I have attached the file with macros. I was starting with Discharged Patients but ideally will need it to work for in-house patients, too. This list will be a running list, so one patient may have multiple visits and I can't remove based on their name alone.
Short version: filter on Master List for "D/C" or "IH", copy and paste to appropriate sheet. Remove duplicates UNLESS the macro can copy only NEW D/C or IH patients and paste below items already pasted.

I appreciate your help!
THANK YOU!!!
Katrina

 

[Nebu]

Hi:

This Macro will filter for Discharge & In-house from Mater list , copy and move to respective tabs on clicking the button move. However, I have not incorporated the filter for new records as I am not sure about the criteria to apply there.

Sub TestDC()
Application.ScreenUpdating = False

Dim rng1 As Range

j& = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1
k& = Sheet4.Cells(Rows.Count, "A").End(xlUp).Row + 1

With Sheet1
i& = .Cells(Rows.Count, "A").End(xlUp).Row
Set rng1 = .Range("A2:Q" & i)
.Range("$A$1:$Q" & i).AutoFilter Field:=2, Criteria1:="D/C"
rng1.SpecialCells(xlCellTypeVisible).Copy
Sheet3.Range("A" & j).PasteSpecial xlPasteValuesAndNumberFormats
.Range("$A$1:$Q" & i).AutoFilter Field:=2, Criteria1:="IH"
rng1.SpecialCells(xlCellTypeVisible).Copy
Sheet4.Range("A" & k).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End With

Application.ScreenUpdating = True
End Sub

Thanks

 

[klloyd76]

Thank you - this worked! As for criteria for new records...that's why I was thinking about "removing duplicates" or only paste new items? I am not familiar with all the options in excel for something like this. I need an "IF" B,B=D/C AND MRN is unique, then paste row .... or something like that... I am open to options!

 

[Nebu]

Hi:

if you have logic to identify the new records , I can translate that into VBA code. You necessarily do not need to know VBA.

Thanks

 

[klloyd76]

Hi, I don't have specific logic. I could do copy and paste all dicharges so it overwrites all OR looks for matching patient name and discharge dates and not paste if there's a match.
Thank you for looking at this!!

 

[Nebu]

Hi:

I have taken the easy way here, the attached macro will clear all the contents in In-house and Discharge tab and will copy and paste the contents from Master List based on the filters applied.

Thanks

 

[klloyd76]

That is absolutely perfect! THANK YOU SO MUCH!!
Katrina