Locating the Pivot Table 1

[shaunk]

I have the following code to create a pivot table:

Sub Pivot_Table()
    MyDest = "Sheet20!R13C1"
    MySource = "Sheet1!R1C1:R6C5"
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R6C5").CreatePivotTable TableDestination:="", _
tablename:="Pivottable1"

    ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Org_Name", _
        "Data"), ColumnFields:="Start_Range"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("New_Referrals")
        .Orientation = xlDataField
        .Caption = "Referrals"
        .Position = 1
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("New_Clients_Seen")
        .Orientation = xlDataField
        .Caption = "Clients Seen"
        .Function = xlSum
    End With
    Sheets("Sheet1").Select
       

End Sub

It works OK, but what I would like it to do is position the pivot table in the same location every time. Of course, I would have to delete the exisiting pivot table first. Once I use the "mydest" substitution in setting tabledestination, the macro fails and highlights the block?.

Any ideas would be appreciated.

 

[xlbo]

why re-create the pivottable when you already have one ???

Just update the source data and refresh

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[vbap]

mydest is a literal string and the variable parameter needs a range. Try:

Tabledestination:=range(mydest)

or

Tabledestination:=worksheets("Sheet20").cells(13,1)

or

you can do what xlbo said.