Excel 2007 Pivot Table VBA

[slink9]

Hello,

I am having (and always have had) a problem with Pivot Tables. Now that I have a decent understanding of utilizing Pivot Tables from the menu I can't get the VBA code set up to create and maintain one.

Sub central_DP_pivot()
    Dim wks As Worksheet
    Dim pvc As PivotCache
    Dim pvt As PivotTable
    Set wks = Worksheets.Add(after:=Sheets(Sheets.Count))
    wks.Name = "Pivots"
    Dim r As Range
    Dim botrow As Integer
    Sheets("CENTRAL_DP").Select
    botrow = Sheets("CENTRAL_DP").Range("A1").End(xlDown).Row
    Set r1 = Sheets("CENTRAL_DP").Range("A1:A" & botrow)
    Set r2 = Sheets("CENTRAL_DP").Range("C1:C" & botrow)
    Set r3 = Sheets("CENTRAL_DP").Range("H1:H" & botrow)
    Set r4 = Sheets("CENTRAL_DP").Range("P1:P" & botrow)
    Set r5 = Sheets("CENTRAL_DP").Range("Z1:Z" & botrow)
    Set r6 = Sheets("CENTRAL_DP").Range("AC1:AC" & botrow)
    Set fullrng = Union(r1, r2, r3, r4, r5, r6)
    fullrng.Select
    Set myrange = Selection
    Names.Add Name:="Database", RefersTo:=myrange
    Sheets("Pivots").Select
    Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Database")
    Set pvt = pvc.CreatePivotTable(TableDestination:=wks.Range("A3"), DefaultVersion:=xlPivotTableVersion12)
    
End Sub

Things seem to work fine until the last line of this code. It gives me an error - "Reference is not valid" and I can't find out what/where/how. Any ideas and suggestions?

 

[slink9]

Okay, that one is solved - I was trying to set up a range of non-adjacent columns to use. That was not necessary and was the dource of this problem ... using A1:AC?? as the range worked perfectly.