Create a pivot using Excel macro

Question

Please help with how to create a pivot with the filter of data using excel macro.

Answer 1

Hi,

scenario

the pivot tbl exists

(in one sheet, source data range and Piv.tbl)

using autofilter in data range

result pic-1

after run the below code

result pic-2

vba macro

Sub PivTbl_Hide_Items()

Dim ws1 As Worksheet

Set ws1 = Sheets("Sheet1") ' sht name

Dim rng As Range, rFind As Range, rngField As Range, r As Range

Dim row1, nRow, nCol

Set rng = ws1.Range("A1:H19") '<< data range

row1 = rng.Item(1).Row

nRow = rng.Rows.Count

Dim pt As PivotTable

Dim pf As PivotField

Set pt = ws1.PivotTables(1)

Dim x, i

Dim fg As Boolean

For Each pf In pt.PivotFields

pf.ClearAllFilters

Next

With pt

.PivotCache.MissingItemsLimit = xlMissingItemsNone

.PivotCache.Refresh

End With

Application.ScreenUpdating = False

i = 1

For Each pf In pt.PivotFields

If ws1.AutoFilter.Filters(i).On Then

Set rFind = rng.Item(1).Resize(, nCol).Find(pf.Name)

Set rngField = ws1.Cells(row1 + 1, rFind.Column).Resize(nRow - 1)

For x = 2 To pf.PivotItems.Count

pf.PivotItems(x).Visible = False

Next x

fg = False

For Each r In rngField.SpecialCells(xlCellTypeVisible)

If pf.PivotItems(1).Value = r.Value Then

fg = True

End If

pf.PivotItems(r.Value).Visible = True

Next r

If fg = False Then pf.PivotItems(1).Visible = False

End If

i = i + 1

Next pf

Application.ScreenUpdating = True

End Sub

Answer 2

Please describe your situation in more detail.

.

Why do you want to use a macro to create the PivotTable?

What kind of data processing will you be doing in the macro?

Why do you want to filter the data before putting it in the PivotTable? Will anyone "ever" want to see that data that has been filtered, or is it totally invalid, never to be used again?

You know you can use filters/slicers inside the pivot table.

.

As an alternative to a macro, I'm thinking use PowerQuery to import the raw data, filter it, if necessary, the define a PivotTable on the output. Like a macro, you only have to define the Query and PivotTable once, then you can import new data by 'refreshing" the PivotTable.

Answer 3

Hello Narayandatta,

Thank you for coming to the forum. See the links below and I do hope they help you out.

https://www.automateexcel.com/vba/pivot-table-filter/

https://officetuts.net/excel/vba/filter-excel-pivot-table-using-vba/

Note: Those are non-Microsoft websites. The pages appears to be providing accurate, safe information. Watch out for ads on the sites that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the sites before you decide to download and install it.

Give a reply

Best Regards Fuad