Pivot Table source update

Question

I have looked at previously answered questions on this subject and tried to apply the answers to my macro but I keep getting run time errors (13)when I try to apply it.I would appreciate any help.

Code:

Sub TestPivot()

Dim test1 As Range

Set test1 = Sheets("Raw Data").Range("B1:T100000")

       Sheets("Summary").PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        test1 _
        , Version:=xlPivotTableVersion14)

Set test1 = Nothing
End Sub

Thanks,

Ciaran.

ejlj · Accepted Answer · 2014-08-13 13:19:03Z

1

Try this and see if it works:

Sub TestPivot()
       Dim test1 As String

       test1 = "'Raw Data!R1C2:R10000C20'"

       Sheets("Summary").PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        test1 _
        , Version:=xlPivotTableVersion14)

        test1 = ""
End Sub

edited Aug 13, 2014 at 13:19

answered Aug 12, 2014 at 18:38

ejlj

1853 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1624926 Over a year ago

I added the missing " and I got it to work. I really apreciate your help.

ejlj Over a year ago

Yep, sorry about that, I didn't realized that

Collectives™ on Stack Overflow

Pivot Table source update

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related