Consolidation of Worksheet into a final worksheet

Rahul Roy
Rahul Roy used Ask the Experts™
I would like to create a a worksheet tab or consolidation tab where data from Raw data tab always gets appended without duplicates based on a particular column, lets say column 47
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you upload sample and show expected result?
Managing Director/Excel VBA Developer
Distinguished Expert 2018

Assuming data in Raw Sheet starts from A2 until Col 47, below VBA will copy data from Raw Sheet to Consolidation Sheet and removes duplicate from Col 47:
Sub Update_Consolidation()
Dim RwWs As Worksheet, ConsWs As Worksheet
Dim RwLR As Long, ConsLR1 As Long, ConsLR2 As Long
Dim DelRng As Range
Set RwWs = Worksheets("Raw")
Set ConsWs = Worksheets("Consolidation")
RwLR = RwWs.Range("A" & Rows.Count).End(xlUp).Row
ConsLR1 = ConsWs.Range("A" & Rows.Count).End(xlUp).Row
RwWs.Range("A2:AU" & RwLR).Copy
ConsWs.Range("A" & ConsLR1 + 1).PasteSpecial xlPasteValues
ConsLR2 = ConsWs.Range("A" & Rows.Count).End(xlUp).Row
With ConsWs
    Set DelRng = Range("AU2:AU" & ConsLR2)
    DelRng.EntireRow.RemoveDuplicates Columns:=Array(47)
End With
End Sub

Open in new window

Let me know if you need any changes.
Rahul RoyData Scientist


Thanks this is working fine for me :)
Rahul RoyData Scientist


Thanks for helping by :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial