Excel - Como asignar los datos de varias celdas en una sola fila MACRO

Sub Macro1()

    Dim Fila_Orig As Long, Orig As Worksheet, Dest As Worksheet, _

        Fila_Dest As Long

   Set Orig = Sheets("Hoja 1 - Antes")

   Set Dest = Sheets("Hoja 2 - Despues")

    For Fila_Orig = 2 To Orig.Range("A" & Rows.Count).End(xlUp).Row

        If Len(Trim(Orig.Cells(Fila_Orig, "A"))) > 0 Then

            Fila_Dest = Fila_Dest + 1

            Dest.Cells(Fila_Dest, "A") = Orig.Cells(Fila_Orig, "A")

            Dest.Cells(Fila_Dest, "B") = Orig.Cells(Fila_Orig, "B")

            Dest.Cells(Fila_Dest, "C") = Orig.Cells(Fila_Orig, "C")

            Dest.Cells(Fila_Dest, "D") = Referencia(Fila_Orig, Orig)

        Else

            Dest.Cells(Fila_Dest, "D") = Dest.Cells(Fila_Dest, "D") & " " & Referencia(Fila_Orig, Orig)

        End If

    Next

End Sub

Private Function Referencia(Fila_Orig, Orig) As String

    Dim Tabla() As String, Texto As String

    Texto = ""

    If Len(Orig.Cells(Fila_Orig, "D")) > 0 Then

        Tabla = Split(Orig.Cells(Fila_Orig, "D"), " ")

        For a = 0 To UBound(Tabla)

            If Len(Tabla(a)) > 0 Then Texto = Texto + Tabla(a) + " "

        Next

    Else

    Referencia = Left(Texto, Len(Texto) - 1)

End Function