asked on

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
Calculate

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

ActiveWorkbook.Save

    Sheets("DessertData").Select
    Range("A3").Select
    
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

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

    ActiveWorkbook.Close

    Windows(OrigWB).Activate

    
    Sheets("DessertData").Select
    Range("A2").Select
    MsgBox "Refresh Complete and Send File Complete", vbOKOnly
End Sub

Open in new window

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Briad IS

ASKER

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.