Guru do Excel e Power BI

Sub AleVBA_6344()
'Este arquivo deve ficar fora do diretório \TESTE\
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim lr As Long
lr = Range("A1").SpecialCells(xlCellTypeLastCell).Row
Dim LastRow As Long
Const strPath As String = "C:\Users\Administrador\Downloads\TESTE\"
    wkbDest.ActiveSheet.Range("A1").CurrentRegion.Clear
    [A1].Value = "Placas": [B1].Value = "Valor"
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("Plan1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("Plan1").Range("A1:A" & LastRow).Copy wkbDest.Sheets("alevba").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Range("B2").Formula = "=COUNTIF($A:$A,A2)"
    Range("B2").AutoFill Destination:=Range("B2:B" & lr)
    With Range("A1:B100")
        .Value = .Value
    End With
    Worksheets("alevba").Range("B1").AutoFilter Field:=2, Criteria1:="1"
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = False
    ActiveSheet.[A1].CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveSheet.ShowAllData
    'Use o With Statement acima, para evitar repetição
Application.ScreenUpdating = True
End Sub