Briad IS
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.
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER