VBA EXCEL Create a pivot table with all populated cells

Question

I'm trying to create in FILTERED worksheet a pivot table from all non-empty cells in ALL_RECORDS worksheet,(populated cells will be different every day), as follows :

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ALL_RECORDS!R1C1:R3992C19", Version:=6).CreatePivotTable _
        TableDestination:="FILTERED!R3C1", TableName:="TablaDinámica1", _
        DefaultVersion:=6
    Sheets("FILTERED").Select
    Cells(3, 1).Select

But I keep getting error 5 (Invalid procedure call or argument) Even if i do it by saving a macro... I need to create the pivot table each time the macro runs. Any help will be greatly appreciated. Thanks in advance!

What do you mean -- "populated cells"? All visible (not filtered out) or all non-empty? Secondly, is it an option to create the PivotTable by hand (without VBA) and just creating a macro which refreshes the data when you put new lines in ALL_RECORDS? — nicolaus-hee
hi @nhee thanks for answering, yes, all non-empty cells will be the source of the pivot table, and, unfortunately ,the pivot table needs to be created each time the macro runs... — user9910379

MikeR MikeR · Accepted Answer · 2019-03-16T13:33:49

I tried to reproduce your error code, and the only case i have this error is when the sheet "FILTERED" doesn't exist. You have to create it before launching the creation of pivot table.

Example :

Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "FILTERED"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "ALL_RECORDS!R1C1:R3992C19", Version:=6).CreatePivotTable _
    TableDestination:="FILTERED!R3C1", TableName:="TablaDinámica1", _
    DefaultVersion:=6
Sheets("FILTERED").Select
Cells(3, 1).Select

VBA EXCEL Create a pivot table with all populated cells

1 Answers