title: Excel 2010 VBA sheet copy reformats dates (non US format: i.e. dd/mm/yyyy)
body: <p>I am trying to copy a either an entire worksheet or a range of data from one workbook to another. I can do this successfully manually, by selecting all the cells with data in them and pasting into a newly created worksheet (Date format is maintained). However when I try to do this via VBA any date with a day greater than 12 gets converted to text [US (m/dd/yyyy)/Australian (dd/mm/yyyy) format change]. I've tried formating the cells first to US format dates but this doesn't help. Any sugestions? </p>
  
  <p>FYI - source data is from a CSV file that is opened by VBA - date values are text (eg "2/08/2013" or "31/07/2013") that are automatically converted to dates on open (IsNumber = True and IsText = False).</p>
  
  <p>CSV on open looks like this (sample of 3000 odd lines):
  Note that "Date" and "Last update" fields are in dd/mm/yyyy hh:mm format</p>
  
  <pre><code>Id          Title     Division  Status  Date                Last update
  REQ:7619    Job Details ICTIS   InProg  31/07/2013 13:03    6/08/2013 15:51
  REQ:7617    Job Details ICTIS   InProg  31/07/2013 12:06    2/08/2013 11:34
  REQ:6994    Job Details ICTIS   Open    31/07/2013 12:05    31/07/2013 12:05
  REQ:7613    Job Details MNHD    User    31/07/2013 12:01    1/08/2013 15:59
  INC:79210   Job Details ICTIS   Open    31/07/2013 12:00    31/07/2013 12:00
  </code></pre>
  
  <p>Simple excerpt of Code:</p>
  
  <pre><code>'Select all Data Cells
  Sheets(1).Cells(1, 1).Activate
  ActiveCell.SpecialCells(xlLastCell).Select
  LastRow = ActiveCell.Row
  
  'Format Date Fields to avoid automatic translation issue
  'This does not help problem... so commented out
  'Columns("M:M").Select
  'Selection.NumberFormat = "m/d/yyyy"
  
  'Select all data in sheet and copy
  range("A2:" &amp; LastCol &amp; LastRow).Select
  selection.Copy
  
  'Rest of code moves to previous sheet and copies into cell A8
  </code></pre>
  
  <p>Same file after running the above code.
  Note how dates with day>12 are treated differently to those with day&lt;=12. 31/7/2013 has "PM" added, while 6/8/2013 and 2/8/2013 have day and month swapped!</p>
  
  <pre><code>Id          Title     Division  Status  Date                Last update
  REQ:7619    Job Details ICTIS   In Progress 31/07/2013 1:03:49 PM   8/06/2013 15:51
  REQ:7617    Job Details ICTIS   In Progress 31/07/2013 12:06:31 PM  8/02/2013 11:34
  REQ:6994    Job Details ICTIS   Open    31/07/2013 12:05:50 PM  31/07/2013 12:05:50 PM
  REQ:7613    Job Details MNHD    With User   31/07/2013 12:01:46 PM  8/01/2013 15:59
  INC:79210   Job Details ICTIS   Open    31/07/2013 12:00:52 PM  31/07/2013 12:00:52 PM
  </code></pre>
  
  <p>My quick fix has been to split the code into 2 parts - with a manual copy/paste prompted by a MsgBox. Not very elegant!! and prone to error.</p>