Date Format VBA code

[HikingGiraffe]

I have searched the internet tirelessly for an answer to this question that I desperately need more understanding on.
What I am trying to do is create a macro that will take raw data on one sheet and put it into a pivot table on a new sheet - pretty standard procedure right?

The issue is that I cannot, for the life of me, figure out how to make the dates in column C read in date format instead of default text or general format. On the pivot table, it is asking the user to sort A-Z or Z-A which sorts by the month / numerical order instead of newest to oldest which is what I am looking to do.

I am well aware of all the manual fixes to this, but the intention is for this sheet to be opened, data to be imported, and the table to be ready to analyze.
So with that being said, this is the section of the code I am working with:

Private Sub Workbook_Open()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Worksheets("Aggregate View").Range("A1:Y300").Copy
ThisWorkbook.Worksheets("AggregateView").Range("A1").PasteSpecial xlPasteValues
OpenBook.Close False
End If
Dim datrange As Range
Set datrange = Range("C9:C500")
datrange.NumberFormat = "MM/dd/yyyy"
'The following line will run Macro2
Call WageSummaryPivotTableVersion1_1
End Sub

I am not getting any error codes, and it is changing the selected cells but it's changing them to a custom format that isnt properly reflecting date format in the pivot table.

Please feel free to massacre my code, I am brand new at VBA and would love to hear any and all suggestions for improvement.

Thank you!

 

[HikingGiraffe]

It's fixed!

I took a couple days to really look into your suggestions and you're right, it was a data issue not a macro issue.
I recorded a new pivot table macro that included text to columns > fixed width > Date format MDY.
I had been messing around with text to columns in the beginning of this project but always chose delimited instead of fixed width. I never would have tried going back to working with my aggregate data if you hadn't helped me run through all my options.

Through my extensive internet search for an answer, I know this is a common issue with pivot tables, so I really hope this thread helps someone else in the future.

It is such a relief to actually see my dates in order after weeks of trying to fix the issue. I cant thank you enough!!

p.s. not that it matters now, but I am in Central Standard Time