Copy tabs to new workbook in 2016 loses formatting

Macro in Excel 2016 does not behave the same as it did in 2010.

We have just upgraded our version of Office from 2010 to 2016 and have found the results of taking tabs from one spreadsheet, copying them to another causes the formatting to be lost.  The changes are most noticed when pivot tables are involved.

In 2010:
Have a data tab, then one or more tabs with pivot tables which are then copied off to another workbook. Select all tabs, and all cells, Paste Special Values which would keep the formatting and looks of the pivot tables without all the data and bulk of the pivot tables.

In 2016:
Have a data tab, then one or more tabs with pivot tables which are then copied off to another workbook. Select all tabs, and all cells, Paste Special Values which would keep the layout of the data, but all formatting is gone.  All lines and number formatting, percentages now show as decimals.

Below is a macro used in one of my reports which works fine in 2010 but not in 2016.   I have some reports with as many as 30 tabs that would be copied off and sent out, so this issue has a big impact on our reporting.

Sub RefreshandCreate()
' RefreshandCreate Macro

    Dim OrigWB As String
    Dim NewWB As String
    Dim Path As String

    OrigWB = ActiveWorkbook.Name
    Path = ActiveWorkbook.Path
    NewWB = "TGIF Appetizer and Dessert Percentage Report P" & Range("rptMo") & "W" & Range("WkMo") & " " & Range("FY") & ".xlsx"


Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

    Sheets(Array("DessertPercentage", "AppetizerPercentage")).Select
    Sheets(Array("DessertPercentage", "AppetizerPercentage")).Copy
    Sheets(Array("DessertPercentage", "AppetizerPercentage")).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Dim nName As Name
    Dim NNameName As String
    On Error Resume Next
    For Each nName In Names
        NNameName = nName.Name
    Next nName
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
            Path & "\" & NewWB, FileFormat:=51, _
            Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
            , CreateBackup:=False
    Application.DisplayAlerts = True



    MsgBox "Refresh Complete and Send File Complete", vbOKOnly
End Sub

Open in new window

Briad ISDirector, ISAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Well, I don't remember how 2010 handled it, but you explicitly say

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False

to copy only values without formatting. Thus take a look at the other possible values for the Paste parameter.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
To copy values and the format you will need to use PasteSpecial method twice like below...

Selection.PasteSpecial xlPasteValues
Selection.PasteSpecial xlPasteFormats

Open in new window

BTW using too much of Selection is not a good practice unless it is really necessary.
Declare workbook/worksheet objects and set them and you can perform all the steps without selecting the workbook or worksheet.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Briad ISDirector, ISAuthor Commented:
In order to get this to work, I had to first paste the values, return to the original workbook and re-copy the selection, then paste the formats.  Not sure why, but after pasting the values, the information is "cleared" from the clipboard.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.