Home

VBA will not run - Compile error sub or function not defined?

frankb3892
Occasional Contributor

Hello,

 

I tried to use the VBA macro identified here: 

 

https://www.microsoft.com/en-us/microsoft-365/blog/2010/09/21/auto-format-pivottables-to-match-sourc...

 

 

However when i add it to my personal workbook module and run it on a pivot table it gives me the error compile error sub or function not defined.

 

I hope someone who is much more of an expert in VBA can help, this can save me endless hours.

 

image.png

Full Code attached.

 

 

2 Replies

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

 

THANK YOU!!!!

Worked perfectly, I owe you a drink :)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies