Pasting Pivot Table as Values... losing Borders and formatting

Sept 18, 2018 - Here's VBA that copies values and formats for an entire sheet (including pivots) to a temporary sheet and saves that sheet as a new file with the same name as the tab. I'm a VBA hack, so any suggested improvements to this code are appreciated. Good luck and thanks in advance for any feedback on this coding.

Sub CopySheetsToFile()

'This macros copies the selected to sheet to a new sheet with similar formatting and with formulas/links/pivots removed

    Dim SourceSheet As Worksheet

    Dim TempSheet As Worksheet

    Dim NewName As String

    If MsgBox("Copy specific sheet to a new workbook" & vbCr & _

    "New sheet will be pasted as values, named ranges removed" _

    , vbYesNo, "NewCopy") = vbNo Then Exit Sub

    With Application

       .ScreenUpdating = False

'       Modified - Original Source:http://www.vbaexpress.com/kb/getarticle.php?kb_id=359

'       Copy active sheet

        Set SourceSheet = ActiveWorkbook.ActiveSheet

'   ***Insert new temporary sheet-per https://www.mrexcel.com/forum/excel-questions/607515-copy-paste-pivot-table-values-formatting.html

       Set TempSheet = ThisWorkbook.Worksheets.Add

       SourceSheet.Select

       Cells.Select

       Selection.Copy

       TempSheet.[A1].PasteSpecial Paste:=xlPasteValues

       SourceSheet.Select

       Cells.Select

       Selection.Copy

       TempSheet.[A1].PasteSpecial Paste:=xlPasteFormats

       Application.CutCopyMode = False

'      File name equal to sheet name

       TempSheet.Copy

       NewName = SourceSheet.Name

'      Formatting of TempSheet

       Range("A1").Select

       ActiveWindow.DisplayGridlines = False

       ActiveWindow.Zoom = 70

       ActiveSheet.Name = SourceSheet.Name

'     Save it with the NewName and in the same directory as original

        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & NewName & ".xlsx" _

        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

        ActiveWorkbook.Close SaveChanges:=False

        .ScreenUpdating = True

 '    ***Delete temporary Worksheet-This section may not be needed

       Application.DisplayAlerts = False

       TempSheet.Delete

       Application.DisplayAlerts = True

       Set TempSheet = Nothing

    End With

    Exit Sub

End Sub

'    ***NOTE 1: This macro should be saved with the Workbook (vs Personal Macro Workbook) for the resulting new file to be saved in the original directory.

 '    ***NOTE 2: To work on a Mac, the above SaveAs command needs to be replaced by: ActiveWorkbook.SaveAs "Macintosh HD:Users:USERNAMEHERE:Desktop:DESKTOPFOLDERNAMEHERE:" & NewName & ".xlsx", FileFormat:=51

I've had a look at various Excel versions I have at my disposal.

Excel 2003: Format is retained

Excel 2010: Format is lost

Excel 2013: Format is lost

Excel 2016 Monthly channel: Format is lost

Excel 2016 Insider fast: Format is lost

Hey Stranger :-)  This only recently started happening to me also. Just "upgraded" to 365 at work, and this started happening. Was on 2013 prior to that and no issues. I have a little routine attached to a a key combo that hardwires whatever I have selected, and I do this a lot with Pivot tables. Been a major PITA for me.

Tried Debs' workaround from John W, of using the Office clipboard and found that I get different results if I do or don't have the Filters selected when i do the copy instead of just the table of values. Just strange.

That aside, I have to believe this was an unintended consequence of some other change in the code by the Devs, as surely no-one in their right mind would simply assume that this was a logical thing to do?

That aside, hope you are all well.

Regards

           Ken.....................

Need to check Hayhams’s method proposed above.... but rest assured, we’ve been using the latest builds (updated automatically) and this just started this month for us.