VBA - Create and format a pivot table

[docsmitty]

Hi everyone, this is my first post and it's a doozy (from my perspective!) I've used Excel for a long time, but never created a macro or used VBA before. I'm applying for a job, and part of the application is an open-book Excel test. I need to write a macro that creates and formats a pivot table to their specifications.

I have begun this task by recording a macro that does these things to their specifications. However, when I try to run the macro, I get errors and do not know how to fix it. The assignment specifically says that I may ask for assistance anywhere and everywhere I see fit (I've considered calling the hiring manager and asking him, but I'm not sure that's what they had in mind )

I'm sure I will have more questions as I refine this macro, but I would seriously appreciate any help I can get!

I am using Excel 2011, and will try this on Excel 2010 on a terminal server I have access to.

It gets stuck on the line that reads:

.Position = 2

and it does not like the custom text formatting:

ExecuteExcel4Macro _
"PIVOT.FIELD.PROPERTIES(""PivotTable"",""Sum of Sales"",,,,Array(,,,"_($* #,## _
0_);_($* (#,##0);_($* ""-""??_);_(@_)"))"



Here is the macro:


Sub FruitSales()
'
' FruitSales Macro
'

'
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R13C3", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R1C10", TableName:="PivotTable", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(1, 10).Select
ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
"PivotTable").PivotFields("Product"), "Count of Product", xlCount
With ActiveSheet.PivotTables("PivotTable").PivotFields("Sales Rep")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
"PivotTable").PivotFields("Sales"), "Sum of Sales", xlSum
With ActiveSheet.PivotTables("PivotTable").PivotFields("Count of Product")
.Orientation = xlColumnField
.Position = 2
End With
ExecuteExcel4Macro "(1,""PivotTable"",4,TRUE)"
ActiveSheet.PivotTables("PivotTable").TableStyle2 = "PivotStyleLight22"
ExecuteExcel4Macro "(""PivotTable"",1)"
Range("K3").Select
ExecuteExcel4Macro "(1,""R3C14:R6C14"",1,1,1)"
ExecuteExcel4Macro _
"PIVOT.FIELD.PROPERTIES(""PivotTable"",""Sum of Sales"",,,,Array(,,,"_($* #,## _
0_);_($* (#,##0);_($* ""-""??_);_(@_)"))"
ExecuteExcel4Macro "(""PivotTable"","""",0,FALSE,TRUE)"
ExecuteExcel4Macro "(""PivotTable"","""",0,FALSE,TRUE)"
ExecuteExcel4Macro "(""PivotTable"","""",0,FALSE,TRUE)"
ExecuteExcel4Macro "(""PivotTable"","""",0,FALSE,TRUE)"
ExecuteExcel4Macro "(""PivotTable"","""",0,FALSE,TRUE)"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

 

[docsmitty]

Well, trying again in Excel 2010 gave me much better results. It's a lot cleaner and so far it works.

Now, what I need is to have it accept a variable number of rows as inputs. I've seen some formulas to do this, and will give it a shot. Again, any feedback appreciated!


Sub FruitSales()
'
' FruitSales Macro
'

'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R13C3", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R1C10", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(1, 10).Select
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Sales"), "Sum of Sales", xlSum
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Rep")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("PivotTable2").TableStyle2 = "PivotStyleLight22"
ActiveSheet.PivotTables("PivotTable2").RowAxisLayout xlTabularRow
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of Sales")
.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
End With
Range("N3").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Rep").AutoSort _
xlAscending, "Sum of Sales", ActiveSheet.PivotTables("PivotTable2"). _
PivotColumnAxis.PivotLines(4), 1
End Sub