Creating pivot table in VBA

Question

I am using the code below to try and create a pivot table in Excel, but it doesn't appear to be working

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

Dim AllData As Range
Set AllData = selection.CurrentRegion

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=AllData).CreatePivotTable TableDestination:= _
        "'[Process Data v2.xls]Pivot'!R4C1", TableName:="PivotTable3", _
        DefaultVersion:=xlPivotTableVersion10

The error is with the pivot table section, and its a runtime error 5. Invalid procedure call or argument.

I have tried recording a macro and having a precise range in the script and it works, but replacing this with the 'Alldata' variable breaks it. I tried a lot of things but can't figure out why its not working

Try just using Set AllData = [A1].CurrentRegion , Remember that if any cells that are touching your data have data they will be included in Currentregion. — user2140261
– user2140261, Commented Nov 12, 2013 at 15:11
Now it's stopped working again. I didn't change any code, all I did was exit the workbook and open it again! — user2983064
– user2983064, Commented Nov 12, 2013 at 16:10
Are you adding a pivot table with the same name as an existing one? — Tim Williams
– Tim Williams, Commented Nov 12, 2013 at 17:58

Tim Williams · Accepted Answer · 2013-11-12 18:01:27Z

2

Try splitting up your code so that the pivot cache and table creation are separated: thet will give you a better idea of where the actual error lies:

Dim pc, pt
Dim AllData As Range

Set AllData = Range("A1").CurrentRegion

Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                                        SourceData:=AllData)
Set pt = pc.CreatePivotTable TableDestination:= _
              "'[Process Data v2.xls]Pivot'!R4C1", _
              TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10

answered Nov 12, 2013 at 18:01

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

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

1 Comment

user2983064 Over a year ago

It doesn't like that code, wont even let me run the vba with it. I think the problem is with the AllData part because the code worked when I recorded it, just when I replaced the hardcoded selection with Alldata

Collectives™ on Stack Overflow

Creating pivot table in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related