E
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
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