title: Excel VBA PasteSpecial not pasting consistently
body: <p>I have a report which is used to import data relating to jobplans and then creates graphs and stats based on the data. Calculations and graphs are based on tables and the tables are populated by VBA - user selects the file and then VBA checks it matches the expected file format and put everything in the right place.</p>
  
  <p><strong>HOWEVER</strong>, the pastespecial part of the code does not paste everything correctly. Specifically there are a number of columns with datevalues and when pasted some of them (not one column or particular rows but seemingly random cells) are not formatted as dates when pasted and therefore are not captured in formulas when I look for job within particular timeframes.</p>
  
  <p>In the source file the all data is 100% saved as a datevalue (if I put a filter on the data, it is all grouped by year and can be expanded to month/day/time + if I use a test cell to do add 1 to the cells that the next date is shown). Once pasted into target sheet then some is still a datevalue but some appears to be text and showing as dd/mm/yyyy hh:mm but being missed from calculation. On these cells if I go onto them press F2 and then Enter then the cell changes to a datevalue (realigns to the right and then gets included in daterange formulas).</p>
  
  <p>Here is the code:</p>
  
  <pre><code>Public Sub importdata()
  Dim wb1, wb3 As Workbook
  Dim ws1, ws3 As Worksheet
  Dim lrow As Long
  Dim WOtable As ListObject
  Dim searchcell As Range
  
  Set wb1 = ThisWorkbook
  Set ws1 = wb1.Sheets("Dashboard")
  Set WOtable = ws1.ListObjects("workorder")
  
  WOfile = Application.GetOpenFilename(FileFilter:="Excel Files (*.CSV),*.CSV", Title:="Select Workorder Extract To Be Opened",MultiSelect:=False)
  If WOfile = False Then Exit Sub
  Set wb3 = Workbooks.Open(WOfile)
  Set ws3 = wb3.Sheets(1)
  
  ws3.Range("M:M, O:O, Q:Q").EntireColumn.Delete
     If ws3.Range("A1").Value = "jobnumber" And ws3.Range("B1").Value ="jobdesc" And etc etc Then
     lrow = ws3.Range("A1").End(xlDown).Row
      ws3.Range("A2:O" &amp; lrow).Copy
     WOtable.DataBodyRange(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     Application.CutCopyMode = False
     Else: MsgBox ("File selected to import workorder information was not in expected format, please check the file and retry.")
  End If
  
  wb3.Close False
  
  End Sub
  </code></pre>
  
  <p>I have tried to add the following line before copying to force it based on something I saw on google but to no avail:</p>
  
  <pre><code>ws3.Columns("E:K").NumberFormat = "DD/MM/YYYY HH:MM:SS"
  </code></pre>
  
  <p>Thanks for any help</p>