Get the common data from multiple sheets in a separate sheet

[sn152]

Hi All,

I am trying to get the common data from multiple sheets in a separate sheet. There are 3 sheets in the attached workbook which contains data. I want to compare all these 3 sheets and pick the rows with common ID numbers and paste it in sheet 4. Please help me with this.

Thanks!

 

[Marc L]

Hi,

update sample workbook Sheet4 with desired result.

Nothing common between these 3 worksheets ! Just two by two …​

 

[sn152]

Hi Marc

What I want is to compare all three sheets and remove the duplicate values and paste the remaining values in sheet 4.

 

[Marc L]

Should I guess ? No ‼
​

 

[Deepak]

Ok got it wait for a while!!

 

[Deepak]

@sn152

Something like this..

Sub test()
Dim ws As Worksheet

With Sheet4
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("A1").CurrentRegion.Offset(1).Copy .Cells(.Range("E1").CurrentRegion.Rows.Count + 1, 5)

    Next

.Range("E2").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlNo

.Range("E2").CurrentRegion.Cut .[A2]

End With

End Sub

 

[Marc L]

Easier with nothing to compare !

Deepak, why not copy directly in column A ?
​

 

[Deepak]

Easier with nothing to compare !

Deepak, why not copy directly in column A ?
​

As i said "Something like this.." so that if col A having nothing itself then paste simply..

Sub test()
Dim ws As Worksheet

With Sheet4
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("A1").CurrentRegion.Offset(1).Copy .Cells(.Range("A1").CurrentRegion.Rows.Count + 1, 1)
    Next

.Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

End With

End Sub

 

[sn152]

Thanks Deepak