Hi Everyone. I'm trying to write my first macro to convert date formats. I have some data that is in an odd date format such as 1980326. In my example the date is 03/26/1998. The first digit, either 1 or 2, needs to be replaced with 19 or 20 and then I want it to be combined and reformatted to mm/dd/yyyy.

Here's what I have so far. I have it insert 7 columns after the current date format (in case there is other data in the file that we don't want to overwrite). It then splits the data, changes the first number, concatenates it, and drags the formula. My problem is coming when I try to copy the data. I'm not sure why but I'm receiving an error with the Range(Selection, Selection.End(x1Down)).Select command. Does anyone know why this is happening? Thanks!

    ActiveCell.Select
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array _
        (5, 1), Array(6, 1)), TrailingMinusNumbers:=True
    Selection.Replace What:="1", Replacement:="19", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="2", Replacement:="20", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveCell.Offset(0, 7).Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[-4],RC[-3],""/"",RC[-2],RC[-1],""/"",RC[-7],RC[-6],RC[-5])"
    Selection.AutoFill Destination:=Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown)).Offset(0, 1), _
    Type:=xlFillDefault
    Range(Selection, Selection.End(x1Down)).Select
    Selection.Copy