Trying to prevent VBA from changing date string into an actual date

[2020914]

Hello -

I have a block of code below which "copies" formulas in row 1 of my worksheet and "pastes" them into the first row of data below the header row. In this case, my header row is row 4 on my worksheet and the data starts in row 5. (I put "copy" and "paste" in quotes because I'm using a range to avoid using actual .copy & .paste functions in my code.)

The code executes perfectly until I hit the final line below. That line works too from a VBA standpoint, but it's changing one of my columns from a string to a date because of .Value. I've tried alternatives such as .Text (this wipes out data from that column completely). I've also tried making sure my Excel formula that is being copied uses the TEXT function, hoping that would keep it as text. But it always changes it to a date when this .Value part of my code executes, and I'm not sure how to stop it.

        'determine start/end columns
        Copy_From_Col = Header_Range.Find("Order_Date", LookIn:=xlValues, LookAt:=xlWhole).Column
        Copy_To_Col = Header_Range.Find("PO Sort Key", LookIn:=xlValues, LookAt:=xlWhole).Column

        'copy/paste formulas        
        Set Copy_Range = .Range(.Cells(1, Copy_From_Col), .Cells(1, Copy_To_Col))
        .Range(.Cells(First_Row, Copy_From_Col), .Cells(First_Row, Copy_To_Col)) = Copy_Range.FormulaR1C1
        
        'autofill & calculate
        .Range(.Cells(First_Row, Copy_From_Col), .Cells(First_Row, Copy_To_Col)).AutoFill _
            Destination:=.Range(.Cells(First_Row, Copy_From_Col), .Cells(Last_Row, Copy_To_Col))
        .Cells.Calculate
        
        'copy/paste values
        Set Copy_Range = .Range(.Cells(First_Row, Copy_From_Col), .Cells(Last_Row, Copy_To_Col))
        .Range(.Cells(First_Row, Copy_From_Col), .Cells(Last_Row, Copy_To_Col)) = Copy_Range.Value   'problem, .Value changes date string to actual date

The column in question is technically a date, but I have it formatted as follows: "2021-05-01". The reason I'm trying to avoid Excel changing this to an actual date (in thise case, 5/1/2021) is because I later use this column to create folders on our shared drive and that's how we want the folder names formatted.

Hope this makes sense and that someone can provide a possible solution.

Thank you!

 

[6StringJazzer]

I suggest using only real dates in your file. Using text to represent dates means they can't be used for anything else but display. Solve the problem downstream where you create the folder names:

FolderName = "My New Folder for date " & Format(Range("A1"), "yyyy-mm-dd")

 

[2020914]

I suggest using only real dates in your file. Using text to represent dates means they can't be used for anything else but display. Solve the problem downstream where you create the folder names:

FolderName = "My New Folder for date " & Format(Range("A1"), "yyyy-mm-dd")

Didn't even consider that, and that's a much better solution. Thank you!