Handling Pivot is difficult in excel VBA itself.
I think BP has deliberately not provided much of it in VBOs.
If I were to suggest, below options are available
a) Know/Learn excel VBA macros and trigger macro from BP
b) Do not use Pivot, but use SUMIFS and COUNTIFS to get values and populate in a pre-formated place.
Take a look here for VBA code for how to create/edit pivot tables. Then create pivot table actions in Excel VBO depending on what you require. Think about which inputs to the code (variables that are declared with Dim) need to be given from your Blue Prism process and which do not.
E.g. for Create Pivot Table, SrcData will need to be defined in your process and you would likely define the worksheet too rather than using ActiveSheet. The workbook name could also be defined.
Start from creating the pivot table then add in features you need, one by one.