Consolidation of Worksheet into a final worksheet

Rahul Roy
Rahul Roy used Ask the Experts™
on
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
Comment
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
Commented:
Hi,

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
ConsWs.Activate
ConsWs.Range("A1").Select
End Sub

Open in new window


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

Author

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

Author

Commented:
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