excel macro to create pivottable

wally32 · Mar 17, 2011

im looking to make my job that bit quicker by introducing a macro to help analysis spreadsheets within a workbook.
within the workbook there will be 12 worksheets (one for each month), where i be analysing some data through a pivottable. creating the pivottable is fine. the information i be looking to analyse will be in column c - h and number of rows maybe different from month to month.
i recorded a macro from start of highlighting cells to creating the final pivottable. when i try the same macro for a different month is comes up with few errors. the macro code i recorded is below
ub Macro2()
'
' Macro2 Macro
' Macro recorded 17/03/2011 by waltek
'
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Feb 2011'!C3:C7").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Score"), "Count of Score", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Score")
.Orientation = xlColumnField
.Position = 1
End With
Sheets("Feb 2011").Select
End Sub

when i run it for the month of march, it comes up with a run time 1004
saying UNABLE TO GET PIVOTFIELDS PROPERTY OF THE PIVOT TABLE CLASS

tried to find what this error means, but couldnot find any solotions

thanks

Andrew Poulsom · Mar 17, 2011

That field is named "Count of Score". What are your column headings?

wally32 · Mar 17, 2011

the pivottable will be something like this.
<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=588><TBODY><TR><TD height=16 width="18%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="15%">
</TD></TR><TR><TD height=16 width="18%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="15%">
</TD></TR><TR><TD height=16 width="18%">Count of Score

</TD><TD height=16 width="13%">Score

</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="15%">
</TD></TR><TR><TD height=16 width="18%">Region

</TD><TD height=16 width="13%">A3

</TD><TD height=16 width="13%">A4

</TD><TD height=16 width="13%">A5

</TD><TD height=16 width="13%">comp deal

</TD><TD height=16 width="13%">D1

</TD><TD height=16 width="15%">Grand Total

</TD></TR><TR><TD height=16 width="18%">Central

</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">3

</TD><TD height=16 width="13%">3

</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">1

</TD><TD height=16 width="15%">7

</TD></TR><TR><TD height=16 width="18%">KAMS

</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">2

</TD><TD height=16 width="13%">3

</TD><TD height=16 width="13%">1

</TD><TD height=16 width="13%">
</TD><TD height=16 width="15%">6

</TD></TR><TR><TD height=16 width="18%">Northern

</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">3

</TD><TD height=16 width="13%">1

</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">1

</TD><TD height=16 width="15%">5

</TD></TR><TR><TD height=16 width="18%">Southern

</TD><TD height=16 width="13%">1

</TD><TD height=16 width="13%">5

</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">3

</TD><TD height=16 width="15%">9

</TD></TR><TR><TD height=17 width="18%">Wales & West

</TD><TD height=17 width="13%">1

</TD><TD height=17 width="13%">2

</TD><TD height=17 width="13%">
</TD><TD height=17 width="13%">
</TD><TD height=17 width="13%">
</TD><TD height=17 width="15%">3

</TD></TR><TR><TD height=17 width="18%">Grand Total

</TD><TD height=17 width="13%">2

</TD><TD height=17 width="13%">15

</TD><TD height=17 width="13%">7

</TD><TD height=17 width="13%">1

</TD><TD height=17 width="13%">5

</TD><TD height=17 width="15%">30

</TD></TR><TR><TD height=16 width="18%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="13%">
</TD><TD height=16 width="15%">
</TD></TR></TBODY></TABLE>

Andrew Poulsom · Mar 17, 2011

What are the headings in C1:G1?

wally32 · Mar 17, 2011

from c1 to g1 the column headers are

region, appellant, dealer/broker, score, U/W,

wally32 · Mar 17, 2011

andrew thanks for your help today on this. i have shortly got to leave work but be back tomorrow. hopefully i can come up with a solution

Andrew Poulsom · Mar 17, 2011

This works for me in Excel 2003:

Code:

Sub Test()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        ActiveSheet.Range("C1").CurrentRegion).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    With ActiveSheet
        .PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
        .PivotTables("PivotTable1").AddFields RowFields:="Region", ColumnFields:="Score"
        .PivotTables("PivotTable1").PivotFields("Score").Orientation = xlDataField
    End With
End Sub

The problem with the recorded code was that C3:C7 was in R1C1 notation, but on running the macro it was interpreted as A1 notation.

wally32 · Mar 24, 2011

hi andrew

sorry for delay in gettingback to you. i have been ill of work. i have just tested this and it works great. exacltly what i am looking for. may thanks for your input and help

kris

wally32 · Mar 24, 2011

how can i add this macro below to current one in this thread so column C automatically changes depending on text in the cell. each macro on its own works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iCol As Integer
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c1:c200")) Is Nothing Then
Select Case LCase(Target.Value)
Case "kams": iCol = 42
Case "central": iCol = 15
Case "northern": iCol = 40
Case "walesandwest": iCol = 4
Case "southern": iCol = 6
Case "retentions": iCol = 31
Case Else: iCol = xlNone
End Select
Target.Interior.ColorIndex = iCol
End If
End Sub

Andrew Poulsom · Mar 24, 2011

Do you want to colour the region cells in your pivot table?

wally32 · Mar 24, 2011

that would be great to if can do that. what i would like to do is format the cells in column c to what ever the user enters in. there would only be those choices that we have got already.
if the pivottable could match those colours then that would be even better.
the format macro works ok on its own, like wise the same goes for the pivottable macro. I thought it would be as easy to copy and paste the format macro below the pivottable and run. but things are never that easy.

excel macro to create pivottable

wally32

New Member

Andrew Poulsom

MrExcel MVP

Excel Facts

wally32

New Member

Andrew Poulsom

MrExcel MVP

wally32

New Member

wally32

New Member

Andrew Poulsom

MrExcel MVP

wally32

New Member

wally32

New Member

Andrew Poulsom

MrExcel MVP

wally32

New Member

Similar threads

Forum statistics

Share this page

excel macro to create pivottable

New Member

MrExcel MVP

Excel Facts

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock