VBA Datum to new sheet Format CopyPaste special Formula CONCATENATE

[Slavio]

Sheet1

Datum	I am very happy because I am	Years	years old
31.3.2021		20
=CONCATENATE(A2;B1;C2;D1)

44286 I am very happy because I am 20 years old

The result in the Sheet2 workbook that I need to achieve

2021-03-31 I am very happy because I am 20 years old

Result copy from Sheet1 to Sheet2 as plain text

Here I came up with something like this:

Sub CopyDatumSpecial()
' The code I have

    ' From Source
    Sheets("Sheet1").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    
    ' To Destination
    Sheets("Sheet2").Select
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    ' Add Formula CONCATENATE
    Range("C4").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-2]C,R[-3]C[1],R[-2]C[2],R[-3]C[3])"
End Sub

And

Sub CopyDatumSpecial2()
' Here I came up with something like this. (
Dim RowCountDate As String
Dim Source As String
Dim Destin As Worksheet

Source.Worksheets("Sheet1").Range(Cells(1, "A"), Cells(RowCountDate, "A")).Copy
Destin.Worksheets("Sheet2").Cells(RowCountTrack, "C").NumberFormat = "yyyy-mm-dd"
Destin.Worksheets("Sheet2").Cells(RowCountTrack + 1, "C").NumberFormat = "yyyy-mm-dd"

End Sub

I don't know how to finish it. Any idea from anyone? Who wants a 20-year birthday today?
Thank you in advance for all the help

 

[Fluff]

How about

=CONCATENATE(TEXT(A2;"dd.mm.yyyy");B1;C2;D1)

 

[Slavio]

How about

=CONCATENATE(TEXT(A2;"dd.mm.yyyy");B1;C2;D1)

Thanks, Formula is OK: =CONCATENATE(TEXT(C2;"dd-mm-yyyy");D1;E2;F1), but How do I apply the formula for the whole column via VBA?

 

[Fluff]

You never mentioned anything about applying it to an entire column, just how to get the date to be formatted.
Also you cannot apply the formula to an entire column when you are referencing cells in the same column.

 

[Slavio]

--

 

[Slavio]

You never mentioned anything about applying it to an entire column, just how to get the date to be formatted.
Also you cannot apply the formula to an entire column when you are referencing cells in the same column.

Yes, he didn't mention it. Sorry. The truth is, I need to create 600 rows below each other, in Sheet2. Column A will contain the formulas that I will paste along with the Range from Sheet1.

So in A2 there will be the first formula and Date will be in C2
This will be repeated.
There will be a formula in A3 and a date in C3
Etc.

 

[Fluff]

Sorry but this is making no sense & is now totally different to your original question & information, so you will need to start a new thread giving accurate details of what needs to happen.
Thanks