title: How to paste special preserving the format in a new WorkBook in VBA
body: <p>I would like copy and paste special (values &amp; <strong>format</strong>) a range from workbook A to workbook B.
  The problem is : the values are paste but not the format</p>
  
  <p>I've tried all the PasteSpecial, but none of it worked...</p>
  
  <pre><code>Sub Macro_copy_paste_pivot()
      Dim date_report As String
      Dim appExcel As Excel.Application
      Dim XLBook As Workbook
  
      Set appExcel = CreateObject("Excel.Application")
      Set XLBook = appExcel.Workbooks.Add
      date_report = WorksheetFunction.WorkDay(Date, -1)
      date_report = Format(date_report, "yyyy-mm-dd")
  
      ' COPY and PASTE the pivot EXO
      Worksheets("Pivot EXO").Activate
      ActiveSheet.PivotTables("Pivot EXO").PivotFields( _
          "[Context].[AsOfDate].[AsOfDate]").VisibleItemsList = Array( _
          "[Context].[AsOfDate].&amp;[" &amp; date_report &amp; "T00:00:00]")
  
      Range("P7:A24").Copy
      XLBook.Sheets.Add.Name = "EXO"
      XLBook.Worksheets("EXO").Range("P7:A24").PasteSpecial Paste:=xlPasteFormats
  
  End Sub
  </code></pre>
  
  <p>So, how can I paste <strong>format</strong> from a workbook A to workbook B?</p>