title: Excel VBA - Saving file changes date format
body: <p>I am trying to automate a process we have to be as smooth as possible by using a macro in Excel to drag formulae down as far as my master data needs, then saving each sheet as a <code>prn</code> file.</p>
  
  <p>The issue is that where there are dates, it is saving the format as American dates so when we upload the data into our system it is either wrong or causes an error (e.g. today I tried to upload <code>30/04/2015</code> but my system said there is no such date. </p>
  
  <p>When I use <code>MONTH()</code> on the cell before AND after running the macro it gives the correct month, but the <code>prn</code> files it has saved down have the date incorrectly stored (the above appears as 4/30/2015 whereas my Excel sheet still says <code>30/4/2015</code>.</p>
  
  <p>I've tried storing the date in US format but it then just retains the format as US format</p>
  
  <p>Below is a MWE of what I have written so far, if anyone could help with how to ensure that dates are stored in the UK format this would be much appreciated.</p>
  
  <pre><code>Sub Uploads()
  
  Dim wb As Workbook, wk As Worksheet, rng As Range, num As Long, i As Long, str As String
  Set wb = ThisWorkbook
  i = 0
  num = wb.Sheets(1).Range("A" &amp; Rows.Count).End(xlUp).Row - 7
  
  For Each wk In wb.Worksheets
      wk.Range("A2:Z2").AutoFill Destination:=wk.Range("A2:Z" &amp; num), Type:=xlFillDefault
      wk.Columns.AutoFit
      wk.SaveAs Filename:="H:\Finance\Uploads\CBCS\Upload Master\CBCS (" &amp; i &amp; ") " &amp; Format(Now, "dd-mm-yyyy") &amp; ".prn", FileFormat:=xlTextPrinter
      i = i + 1
  Next wk
  
  End Sub
  </code></pre>