VBA pivot table Excel 2007 help

[Err]

Thanks for your help...

It's been a while since I've programmed anything in VBA and now I'm into it deep. I used to feel very confident in this but it seems like I've forgotten a thing or two.

I am trying to automate a macro that creates a pivot table. I started by modifying the macro on page 290 of VBA and Macros for MSO Excel 2007.

There are two worksheets. The sheet with the data on it is called: "Team Capacity Data"... the sheet where I would like to place the Pivot Table is called "Dashboard"

Sample/fictitious Team Capacity data (in CSV) is below:

First Name,Last Name,Middle I,B date,Age,Role,Number of hours (Volunteers only),Last Working Day (appox),Program Component,Ethnicity,Gender,National Origins,Life Experience with Poverty,Practitioner experience,Income,other,Skills,Skills,Skills,Skills,Skills,Skills,Skills,Skills,Skills
Eric,Raunig,B,8/4/1969,40,Graduate Assistant,,,I,Caucasian,Male,United States,3,0,13000,,Excel,Statistics,Word,English,German,Access,,,
John,Doe,,12/12/2001,8,Advisor,,,A,African American,Male,United Kingdom,1,14,49000,,Spanish,English,,,,,,,
Jane,Smith,D,1/1/1970,40,Graduate Assistants/Intern,,,A,Asian,Female,Canada,7,9,27000,,French,English,Spanish,Excel,Access,,,,
Joan,Jones,E,1/2/1971,39,Staff,,,A,African American,Female,Uganda,4,15,42000,,English,research,,,,,,,
jay,beardsley,A,2/16/1994,16,Graduate Assistants/Intern,,,H,Indian,Male,United States,6,13,75000,,Word,English,,,,,,,
Mook,Bobery,E,7/13/1997,13,Staff,,,A,Indian,Male,United States,1,9,74000,,English,Word,,,,,,,
Ander,Micton,L,9/6/1997,12,Advisor,,,H,Caucasian,Male,United States,6,7,44000,,English,research,,,,,,,
Rush,Leordon,Y,7/15/1968,42,Volunteer,13,,D,Indian,Male,United States,6,9,35000,,English,Chinese,,,,,,,
Brooper,Darister,V,4/23/1952,58,Volunteer,16,,G,Caucasian,Male,United States,5,4,38000,,English,,,,,,,,
Hanks,Geordor,C,3/23/1992,18,Staff,,,G,Caucasian,Male,United States,6,15,48000,legally blind,Statistics,Management,,,,,,,
Turtiamos,Cord,H,8/27/1991,18,Graduate Assistants/Intern,,,F,Hispanic,Male,United States,2,5,64000,,English,excel,,,,,,,
Robington,Roberiel,D,11/6/1946,63,Advisor,,,D,Hispanic,Male,United States,6,13,33000,,English,Spanish,Word,Pashtoun,Chinese,,,,
Gutthelch,Roberrio,U,1/21/1960,50,Graduate Assistants/Intern,,,B,Caucasian,Male,United States,2,11,33000,,English,Word,,,,,,,
Braig,Jacheal,D,10/22/1979,30,Volunteer,4,12/30/2009,C,Hispanic,Male,United States,3,10,54000,,English,Excel,,,,,,,
Pett,Glene,F,7/4/1996,14,Volunteer,4,,E,African American,Female,United States,7,7,23000,,English,research,German,,,,,,
Eleara,Avadrik,,12/1/1979,30,Volunteer,25,,C,Caucasian,Female,United States,2,5,26000,,English,Access,,,,,,,
Gerthy,Treochka,Y,8/17/1970,39,Advisor,,,C,Native American,Female,United States,1,15,65000,,English,,,,,,,,
Frandra,Xonna,M,5/16/1941,69,Graduate Assistants/Intern,,,B,Indian,Female,United States,2,9,30000,Writing,English,thought,Management,research,Word,Statistics,excel,French,
Dona,Vael,W,3/7/1970,40,Advisor,,,F,Hispanic,Female,United States,1,3,74000,,English,,,,,,,,
Gene,Sortborli,P,10/3/1983,26,Advisor,,,D,Caucasian,Female,United States,3,12,25000,wheelchair,Spanish,English,Access,,,,,,
Alistina,Alina,R,11/10/1989,20,Staff,,,C,Caucasian,Female,United States,6,7,55000,,English,Word,,,,,,,
Tericole,Belgal,M,1/24/1948,62,Graduate Assistants/Intern,,,F,Asian,Female,United States,3,4,57000,,English,Access,,,,,,,
Samara,Wycispeth,U,6/9/1973,37,Advisor,,,H,Indian,Female,Uganda,2,14,50000,wheelchair,English,French,,,,,,,
Danthela,Odach,P,11/4/1948,61,Volunteer,24,11/12/2009,F,Caucasian,Female,United States,6,6,71000,,English,research,,,,,,,
Valerie,Kazihim,X,2/21/1947,63,Graduate Assistants/Intern,,,C,Hispanic,Female,United States,2,6,67000,,English,Excel,,,,,,,


My VBA code is below:

Sub CreateTEAMCapacityEthnicity()
' MrEXCEL book Page 290
Dim WSD As Worksheet
Dim DASH As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim StartRow As Long
Dim StartCol As Long
Dim FinalCol As Long

Set WSD = Worksheets("Team Capacity Data")
Set DASH = Worksheets("Dashboard")


' Delete any prior pivot tables
For Each PT In DASH.PivotTables
PT.TableRange2.Clear
Next PT

' Legacy Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
'FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
'useful for defining edge of board but wont use for this
StartCol = FindCol("Program Component")
FinalCol = FindCol("Ethnicity")

' slow method of defining range, shouldn't have to activate sheet
' Why not: Set PRange = WSD.Range(Cells(1, StartCol), WSD.Cells(FinalRow, FinalCol))
WSD.Activate
Set PRange = Range(Cells(1, StartCol), Cells(FinalRow, FinalCol))

'Set PRange = Range(Cells(1, 9), Cells(26, 10))

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)




' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=DASH. _
Cells(1, 1), TableName:="Pivot1")

' Turn off updating while building the table
PT.ManualUpdate = True

' Set up the row & column fields
PT.AddFields RowFields:=Array("Ethicity"), _
ColumnFields:="Program Component"

' Set up the data fields
With PT.PivotFields("Ethnicity")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With


' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True

'Format the pivot table
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = "PivotStyleMedium10"

DASH.Activate
Range("A2").Select

End Sub

and


Public Function FindCol(columnName As String) As Long
Dim i As Integer

Dim WSD As Worksheet
Dim DASH As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim StartRow As Long
Dim StartCol As Long
Dim FinalCol As Long

Set WSD = Worksheets("Team Capacity Data")
Set DASH = Worksheets("Dashboard")
'Define input area and set up a Pivot Cache
'FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column

For i = 1 To FinalCol
If WSD.Cells(1, i).Value = columnName Then
FindCol = i
Exit For
Else
'will pass bad value to search function. useful for debug
FindCol = 0
End If


Next i


End Function

Thanks again for your assistance

 

[Err]

Oh and I would like the pivot table to be very simple at first ... so I can understand it better. Just something like this:

http://img.photobucket.com/albums/v665/Astrocloud/EXAMPLE.gif

 

[Err]

Any Ideas?

Should I post the error message?

 

[Err]

By poking around I got a working pivot table that does what I want. It seems that the Pivot table is associated with the data that it draws from rather than the spreadsheet that you print it on (the Example from the book prints the pivot on the same page as the data -I don't like the way this looks).

Sub CreateTEAMCapacityEthnicity()
    ' MrEXCEL book Page 290
    Dim WSD As Worksheet
    Dim DASH As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim StartRow As Long
    Dim StartCol As Long
    Dim FinalCol As Long
    
    Set WSD = Worksheets("Team Capacity Data")
    Set DASH = Worksheets("Dashboard")
    
        
    ' Delete any prior pivot tables
    For Each PT In DASH.PivotTables
        PT.TableRange2.Clear
    Next PT
        
    ' Legacy Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    'FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
        'useful for defining edge of board but wont use for this
    StartCol = FindCol("Program Component")
    FinalCol = FindCol("Ethnicity")
    
    ' slow method of defining range.
       WSD.Activate
    Set PRange = Range(Cells(1, StartCol), Cells(FinalRow, FinalCol))
    
    'Set PRange = Range(Cells(1, 9), Cells(26, 10))
    'DASH.Activate
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)
    

    
    
    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=DASH.Cells(3, 1), TableName:="PivotTable1")
    
    ' Turn off updating while building the table
    PT.ManualUpdate = True
   
    ' Set up the row & column fields
    PT.AddFields RowFields:=Array("Program Component"), _
        ColumnFields:="Ethnicity"
    
    ' Set up the data fields
    With PT.PivotFields("Ethnicity")
        .Orientation = xlDataField
        .Function = xlCount
        .Position = 1
    End With
    
    
    ' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    
    'Format the pivot table
    PT.ShowTableStyleRowStripes = True
    PT.TableStyle2 = "PivotStyleMedium10"
    
    DASH.Activate
    Range("A2").Select

End Sub