Problem with Macro in MAC

[EE]

Hi

I have created a simple macro (recorded it). The macro involves the
creation of a Pivot Table from a NamedRange. I have created this in
Excel 2003 and it works like a dream on my computer. But it is not
working in MAC (2004). The error says "Variable Not Found".

I kept away from using Active X or Pivot Charts, etc. Are Pivot Tables
a prolem in MAC too?My code is attached. Any help is really
appreciated.

Best
Prasad

***************************CODE*******************************************
Sub CustIDReport()

Sheets("RawData").Select

******The code is throwing the error at the 3 lines below
(xlpivottableversion10)********

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"RawData!RawData").CreatePivotTable TableDestination:="",
TableName:= _
"CustIDAnalysis", DefaultVersion:=xlPivotTableVersion10

***************The code is is throwing the error at the line above
(xlpivottableversion10)**********


ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.Name = "Analysis By Cust ID"
With ActiveSheet.PivotTables("CustIDAnalysis").PivotFields("Cust
ID")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("CustIDAnalysis").AddDataField
ActiveSheet. _
PivotTables("CustIDAnalysis").PivotFields("Price"), "Sum of
Price", xlSum
ActiveSheet.PivotTables("CustIDAnalysis").AddDataField
ActiveSheet. _
PivotTables("CustIDAnalysis").PivotFields("Cost"), "Sum of
Cost", xlSum
With
ActiveSheet.PivotTables("CustIDAnalysis").PivotFields("Profit")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("CustIDAnalysis").PivotSelect
"Profit[All]", _
xlLabelOnly, True
Range("C3").Select
ActiveSheet.PivotTables("CustIDAnalysis").AddDataField
ActiveSheet. _
PivotTables("CustIDAnalysis").PivotFields("Profit"), "Sum of
Profit", _
xlSum
ActiveSheet.PivotTables("CustIDAnalysis").AddDataField
ActiveSheet. _
PivotTables("CustIDAnalysis").PivotFields("Margin"), "Sum of
Margin", _
xlSum
Range("B3").Select
With ActiveSheet.PivotTables("CustIDAnalysis").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
Range("B4").Select
ActiveSheet.PivotTables("CustIDAnalysis").PivotFields("Sum of
Price"). _
Caption = "SumPrice"
Range("C4").Select
ActiveSheet.PivotTables("CustIDAnalysis").PivotFields("Sum of
Cost"). _
Caption = "SumCost"
Range("D4").Select
ActiveSheet.PivotTables("CustIDAnalysis").PivotFields("Sum of
Profit"). _
Caption = "SumProfit"
Range("E4").Select
ActiveSheet.PivotTables("CustIDAnalysis").PivotFields("Sum of
Margin"). _
Caption = "SumMargin"

'For calculating Margin %

ActiveSheet.Range("F4").Select
ActiveCell.FormulaR1C1 = "Margin %"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-3]"
Range("F5").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.00%"


Application.ScreenUpdating = False
CalcStatus = Application.Calculation
Application.Calculation = xlCalculationManual
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("F5").Copy
Range("F5:F" & LastRow).PasteSpecial _
Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.Calculation = CalcStatus
Application.ScreenUpdating = True

'Formatting

Columns("F:F").Select
Selection.NumberFormat = "0.00%"
Range("F4").Select

''''Some formatting code'''''
End Sub

 

[JE McGimpsey]

Pivot tables aren't, per se, a problem in Mac (not MAC) XL, but Mac VBA
is version 5 while WinXL03 is version 6.x, and there are some other
differences (e.g., no PivotCharts). In Mac XL there is no .Add method
for the workbook's PivotCaches collection object. Instead a PT must be
created by the PivotTableWizard method, which adds the corresponding
PivotCache for the PT to the Collection.

 

[EE]

Hi

I could make it work in MAC using PivotTableWizard Method. I now have
a new problem. I was trying to order the data as "Columns" using this

********
With ActiveSheet.PivotTables("ManufAnalysis").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
**************

We are getting a run-time error in MAC on this.

Thanks for your help
Best
Prasad

 

[JE McGimpsey]

In Mac XL (again, 'Mac'; 'MAC' is typically used as an acronym for Media
Access Control) there is no DataPivotField property (don't have WinXL
available at the moment). The PivotTable object has a DataFields
property which returns a specific PivotField object if used like:

With ActiveSheet.PivotTables("ManufAnalysis").DataFields(1)
.Orientation:=xlColumnField
.Position:=1
End With