Guru do Excel e Power BI

Public Sub Consolidar()
Dim lPlan2 as Long 'percorre as linhas de Plan2
Dim lBusca as Long 'percorre as linhas de Plan1
Dim lResult as Long 'incrementa os resultados consolidados

lPlan2 = 2
lBusca = 2
lResult = 2

'Loop para varrer a tabela maior procurando estes registros
Do Until Plan2.Cells(lPlan2, 1) = ""

'Loop para busca na tabela maior
Do Until Plan1.cells(lBusca,1) = ""

If Plan1.Cells(lBusca,1) = Plan2.Cells(lPlan2,1) Then
Plan3.Cells(lResult,1) = Plan1.Cells(lBusca,1) 'transfere a coluna do cpf de plan1 para plan3
'demais colunas precisam vir dentro desse bloco
lResult = lResult+1
Exit Do
End If

lBusca=lBusca+1
Loop

lPlan2 = lPlan2+1
lBusca = 2
Loop


End Sub

Sub CPFsDuplicados()
 Dim LR As Long
  Application.ScreenUpdating = False
  LR = Sheets("Planilha1").Cells(Rows.Count, 1).End(3).Row
  Sheets("Planilha3").[A:A] = ""
  With ActiveSheet
   On Error Resume Next
   .ShowAllData
   On Error GoTo 0
   .Columns(2).Insert
   .Range("B1:B" & Cells(Rows.Count, 1).End(3).Row).FormulaLocal = "=CONT.SE(Planilha1!A$2:A$" & LR & ";A1)"
   .[A1].AutoFilter 2, ">0"
   .Range("A2:A" & Cells(Rows.Count, 1).End(3).Row).Copy Sheets("Planilha3").[A2]
   .ShowAllData
   .Columns(2).Delete
  End With
End Sub