Hi Frank,
This is because of some invalid characters in the code.
The code contains italic apostrophes and italic double quotes.
Please fix the code as follows:
Sub AdoptSourceFormatting()
'Mike Alexander
'www.datapigtechnologies’
'Be sure you start with your cursor inside a pivot table.
Dim oPivotTable As PivotTable
Dim oPivotFields As PivotField
Dim oSourceRange As Range
Dim strLabel As String
Dim strFormat As String
Dim i As Integer
On Error GoTo MyErr
'Identify PivotTable and capture source Range
Set oPivotTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))
'Refresh PivotTable to synch with latest data
oPivotTable.PivotCache.Refresh
'Start looping through the columns in source range
For i = 1 To oSourceRange.Columns.Count
'Trap the column name and number format for first row of the column
strLabel = oSourceRange.Cells(1, i).Value
strFormat = oSourceRange.Cells(2, i).NumberFormat
'Now loop through the fields PivotTable data area
For Each oPivotFields In oPivotTable.DataFields
'Check for match on SourceName then appply number format if there is a match
If oPivotFields.SourceName = strLabel Then
oPivotFields.NumberFormat = strFormat
'Bonus: Change the name of field to Source Column Name
oPivotFields.Caption = strLabel & " "
End If
Next oPivotFields
Next i
Exit Sub
'Error stuff
MyErr:
If Err.Number = 1004 Then
MsgBox "You must place your cursor inside of a pivot table."
Else
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Sub
