VBA Datum to new sheet Format CopyPaste special Formula CONCATENATE

Slavio

New Member
Joined
Mar 28, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Sheet1
DatumI am very happy because I amYears years old
31.3.202120
=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:
VBA Code:
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
VBA Code:
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,712
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=CONCATENATE(TEXT(A2;"dd.mm.yyyy");B1;C2;D1)
 
Solution

Slavio

New Member
Joined
Mar 28, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. Web
How about
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,712
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 28, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. Web

ADVERTISEMENT

--
 

Slavio

New Member
Joined
Mar 28, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,712
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,879
Messages
5,638,800
Members
417,053
Latest member
SaturdayNight

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top