Multiple csv file import into Excel 2010. US Date issue

[Earl13]

Hello All,

Thank you for letting me join the community, I'm pretty new to building VBA script and am attempting to import a folder of 130 odd csv files in to an workbook and then with a second macro combine them into a single worksheet. The below code is what I am using to bring them into the workbook which works fine apart from changing the date to US format. I have being reading through multiple forums and websites trying to find an answer and trying a whole variety of solution, but nothing seems to work. Please can somebody help to see what I need to do to fix the below VBA or do I need to start again.

Sub CombineWSs()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

MyPath = "R:\Specialist In-Life\USO\4.0 Audit\Internal Audits SR AC1200\Reports\Prior\Todays Files"
Set wbDst = ThisWorkbook
strFilename = Dir(MyPath & "\*.csv", vbNormal)

If Len(strFilename) = 0 Then Exit Sub

Do Until strFilename = ""

Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)

Set wsSrc = wbSrc.Worksheets(1)

wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

wbSrc.Close False

strFilename = Dir()

Loop
wbDst.Worksheets(1).Delete

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

Sheets("Master").Select

End Sub

I think the below is the area I need to change, but how and what to has got me stumped.

Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)

Set wsSrc = wbSrc.Worksheets(1)

wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

wbSrc.Close False

strFilename = Dir()

Thank you in advance.

 

[cerfani]

this is now the reference to the copied sheet in the destination workbook since you put it at then end...

wbDst.Worksheets(wbDst.Worksheets.Count)

so determine the range of your dates and set the format (google the proper format, my code is pseudo code)

after you copy sheet then...

wbDst.Worksheets(wbDst.Worksheets.Count).Range("DateRange").NumberFormat = "MM/DD/YY"