Olá Rodrigo,

seguinte, seu código não está copiando e colando, ele está simplesmente repetindo os dados. Por isso que não está trazendo o resultado desejado.

Outra coisa é, não tem como tirar a barra de uma data, já que o Excel enxerga internamente a data como um número. A alternativa, é
1- visualizar de outra forma (formatando as datas para não mostrar a barra, que é o que eu fiz na coluna da "Data de Entrada")
2- tratar as datas como texto, concatenando o dia com o mes com o ano, neste caso, a data deixa de ser data, e passa a ser um texto que parece o número representante desta data, porém sem as barras (que é o que eu fiz na coluna da "Data de Nascimento")

Quanto aos nomes, eu tomei a liberdade de não removerbarras pontos ou traços, em função das abreviações. Exemplo: Maurício de P. Lima

Mais uma coisa, no seu código, vc fez um loop que varrerá cada linha. Neste caso, esta não é a melhor alternativa, pois se forem muitas linhas, o código pode demorar para rodar.

Dito isso, vamos ao código, pq não adianta só criticar, rs, tem q mostrar a alternativa

Lógica:
Coluna A, via código, eu escrevi uma fórmula na Plan2 que enxerga as mesmas células da Plan1, mas formata-as para DDMMAAAA (ou DDMMYYYY se seu Excel for em inglês)

Coluna B, via código, eu escrevi uma fórmula na Plan2 que enxerga as mesmas células da Plan1, se fazer nada com os nomes (remover pontos, por exemplo)

Coluna C, via código, eu escrevi uma fórmula na Plan2 que enxerga as mesmas células da Plan1, eu converti as datas de nascimento, para Dia (com dois caracteres) concatenados com mês e dois caracteres, concatenado com ano com 4 caracteres. O resultado é quase que equivalente ao resultado da primeira coluna, a coluna A. A diferença é q a coluna A ainda é uma data, e a coluna C virou um texto.

Coluna D, via código, eu escrevi uma fórmula na Plan2 que enxerga as mesmas células da Plan1, mandando o Excel substituir tudo que é barra, ponto ou traço por nada, também usando fórmula do Excel.

NOTA IMPORTANTE: Para usar este código, você precisará trocar os nomes das planilhas de Plan1 e PLan2 para os nomes corretos, no seu arquivo.

O código é este abaixo:

Sub TrazerDadosEspecial()
Dim shtDe           As Worksheet
Dim shtPara         As Worksheet
Dim lUltimaLinha    As Long
    
    With ThisWorkbook
        Set shtDe = .Worksheets("Plan1")
        Set shtPara = .Worksheets("Plan2")
    End With
    
    lUltimaLinha = shtDe.Range("A" & shtDe.Rows.Count).End(xlUp).Row
    shtPara.Range("A2:A" & lUltimaLinha).FormulaR1C1 = "=TEXT('" & shtDe.Name & "'!RC,""DDMMYYYY"")"
    shtPara.Range("B2:B" & lUltimaLinha).FormulaR1C1 = "=""'"" & shtDe.Name & "'!RC"
    shtPara.Range("C2:C" & lUltimaLinha).FormulaR1C1 = "=TEXT(DAY('" & shtDe.Name & "'!RC),""DD"") & TEXT(MONTH('" & shtDe.Name & "'!RC),""MM"") & TEXT(YEAR('" & shtDe.Name & "'!RC),""YYYY"")"
    shtPara.Range("D2:D" & lUltimaLinha).FormulaR1C1 = "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('" & shtDe.Name & "'!RC,""/"",""""),""."",""""),""-"","""")"
    
    Set shtDe = Nothing
    Set shtPara = Nothing
    
End Sub

Vc deve ter visto que eu enfatizei ter feito isso tudo via código, na explicação da lógica. Fiz isso pq não há a necessidade de usar código para isso.
Vc poderia simplesmente escrever as seguintes fórmulas nas seguintes colunas da Plan2, e arrastar para baixo:

Plan2 Célula A2:

=TEXTO('Plan1!A2';"ddmmaaaa")

Plan2 Célula B2:

='Plan1'!B2

Plan2 Célula C2:

=TEXTO(DIA('Plan1!C2');"DD")&TEXTO(MÊS('Plan1!C2');"MM")&TEXTO(ANO('Plan1!C2');"AAAA")

Plan2 Célula D2:

=SUBSTITUIR(SUBSTITUIR(SUBSTITUIR('Plan1'!D2;"/";"");".";"");"-";"")

ok, se acontecer algum erro em linha de código que tenha YYYY, troque por AAAA.
Se houver dúvidas, é só gritar.

Segue também o arquivo anexo com o código funcionando.

Abração,

Bom dia!!

outra forma....

Sub CopiarEspecial_AleVBA()
    
Dim i&, UltimaLinha&, LinhaPlan2&
    
    Application.ScreenUpdating = False
        
    With Sheets("Plan1")
        UltimaLinha = .Cells(Cells.Rows.Count, 1).End(xlUp).Row
        If UltimaLinha < 2 Then UltimaLinha = 2
        LinhaPlan2 = 2
        For i = 2 To UltimaLinha
            If .Range("A" & i).Value <> "" Then
                Sheets("Plan2").Range("A" & LinhaPlan2).Value = _
                    Replace(Replace(Replace(.Range("A" & i).Value, "/", ""), ".", ""), "-", "")
                Sheets("Plan2").Range("B" & LinhaPlan2).Value = _
                    Replace(Replace(Replace(.Range("B" & i).Value, "/", ""), ".", ""), "-", "")
                Sheets("Plan2").Range("C" & LinhaPlan2).Value = _
                    Replace(Replace(Replace(.Range("C" & i).Value, "/", ""), ".", ""), "-", "")
                Sheets("Plan2").Range("D" & LinhaPlan2).Value = _
                    Replace(Replace(Replace(.Range("D" & i).Value, "/", ""), ".", ""), "-", "")
                LinhaPlan2 = LinhaPlan2 + 1
            End If
        Next
    End With
    
    Application.ScreenUpdating = True
End Sub

Veja o anexo... http://www.sendspace.com/file/lo93dm

Att