Refresh specific pivot table and add measures accordingly
Sub PivotRefresh()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim pt As PivotTable
Dim ptField(30) As PivotField
Dim Cur(30) As String
Dim i As Byte
Dim wb As Workbook
Set wb = ThisWorkbook
Set ws = wb.Sheets(PivotSht) 'change sheet name accordingly
Set ws1 = wb.Sheets(InputSht) 'change sheet name accordingly
Set pt = ws.PivotTables(1) 'change pivot number accordingly
'assign measures to array
For i = 1 To 30
Cur(i) = ws1.Cells(2, i + 14)
Next i
With pt
.PivotCache.Refresh
For i = 21 To 30
If i <= 24 Then
Set ptField(i) = .AddDataField(.PivotFields(Cur(i) & "2"), Cur(i) & "2" & " ", xlSum)
.InGridDropZones = True
ptField(i).Position = i - 20
.RowAxisLayout xlTabularRow
Else
Set ptField(i) = .AddDataField(.PivotFields(Cur(i) & "3"), Cur(i) & "3" & " ", xlSum)
.InGridDropZones = True
ptField(i).Position = i - 20
.RowAxisLayout xlTabularRow
End If
Next i
End With
Set ws = Nothing
Set ws1 = Nothing
Set pt = Nothing
End Sub
Code explanation:
यह VBA Macro – PivotRefresh Excel में Pivot Table को Automatically Refresh और Update करने के लिए बनाया गया है। यह code खासतौर पर तब उपयोगी होता है जब data बार-बार बदलता है और आपको Pivot Table में नए Measures / Values जोड़ने हों।
इस Macro में सबसे पहले Workbook और Worksheets को Define किया गया है। PivotSht उस Sheet का नाम है जहाँ Pivot Table मौजूद है, और InputSht वह Sheet है जहाँ से Measures के नाम लिए जाते हैं। इसके बाद Pivot Table को Set किया गया है।
For Loop की मदद से Input Sheet की Row 2 से अलग-अलग Field Names को Array में Store किया जाता है। फिर PivotCache.Refresh से Pivot Table को Refresh किया जाता है, जिससे नया Data Load हो जाता है।
इसके बाद Loop के जरिए Multiple Data Fields (21 से 30 तक) Pivot Table में जोड़े जाते हैं।
अगर Field 24 तक है तो "2" suffix वाले Columns लिए जाते हैं, और उसके बाद "3" suffix वाले Columns जोड़े जाते हैं। सभी Fields को Sum Function (xlSum) के साथ Add किया गया है।
InGridDropZones और RowAxisLayout xlTabularRow से Pivot Table का Layout साफ और Structured बनाया गया है।
यह Macro Automation, Dynamic Pivot Table, VBA for Excel, Pivot Refresh Macro जैसे Keywords के लिए बहुत उपयोगी है और Reporting को आसान बनाता है।
Filter multiple items in Pivot
Sub FilterPivotItems()
Dim PT As PivotTable
Dim PTItm As PivotItem
Dim FiterArr() As Variant
' use this array variable to select the items in the pivot table filter which you want to keep visible
FiterArr = Array("101", "105", "107")
' set the Pivot Table
Set PT = Sheet1.PivotTables("PivotTable1")
' loop through all Pivot Items in "Value" field of the Pivot
For Each PTItm In PT.PivotFields("Value").PivotItems
If Not IsError(Application.Match(PTItm.Caption, FiterArr, 0)) Then ' check if current item is not in the filter array
PTItm.Visible = True
Else
PTItm.Visible = False
End If
Next PTItm
End Sub
Code explanation:
यह VBA Macro – FilterPivotItems Excel में Pivot Table को Automatically Filter करने के लिए उपयोग किया जाता है। इस code की मदद से आप Pivot Table के किसी भी Field में चुने हुए Items को ही Visible रख सकते हैं और बाकी Items को Hide कर सकते हैं।
सबसे पहले Macro में कुछ Variables Declare किए गए हैं जैसे PivotTable, PivotItem और एक Array (FiterArr)। FiterArr में उन Values / Codes को रखा गया है जिन्हें आप Pivot Table में दिखाना चाहते हैं, जैसे "101", "105", "107"।
इसके बाद Set PT = Sheet1.PivotTables("PivotTable1") के जरिए उस Pivot Table को Select किया गया है जिस पर Filter लगाना है। यहाँ आपको Sheet और Pivot Table का नाम अपने अनुसार बदलना होता है।
For Each Loop की मदद से Pivot Table के "Value" Field के सभी Pivot Items को एक-एक करके Check किया जाता है।
Application.Match Function यह Check करता है कि Pivot Item का Caption FiterArr में मौजूद है या नहीं।
अगर Value Array में मिल जाती है तो PTItm.Visible = True कर दिया जाता है, और जो Value Match नहीं करती उसे Visible = False कर दिया जाता है।
यह Macro खासतौर पर Dynamic Reporting, Automated Pivot Filtering, Excel VBA Automation, Pivot Table Filter Macro के लिए बहुत उपयोगी है और Manual Filtering का समय बचाता है।
Add custom sort list and apply on pivot
Sub AddandSort()
' custom sort order
Dim sCustomList(1 To 1) As String
sCustomList(1) = "SEK"
Application.AddCustomList ListArray:=sCustomList
Sheet28.PivotTables("PivotTable1").PivotFields( _
"[PQuery1].[Currency].[Currency]").AutoSort xlAscending, _
"[PQuery1].[Currency].[Currency]"
Sheet29.PivotTables("PivotTable2").PivotFields( _
"[PQuery1].[Currency].[Currency]").AutoSort xlAscending, _
"[PQuery1].[Currency].[Currency]"
End Sub
Code explanation:
यह VBA Macro – AddandSort Excel में Pivot Table को Custom Order में Sort करने के लिए इस्तेमाल किया जाता है। यह code खासतौर पर तब उपयोगी होता है जब आपको Pivot Table में किसी Specific Value को सबसे ऊपर या तय क्रम में दिखाना हो, जैसे Currency, Region या Category।
इस Macro में सबसे पहले एक Custom List बनाई गई है।
Dim sCustomList(1 To 1) As String के जरिए Array Declare किया गया है और उसमें "SEK" Value रखी गई है।
Application.AddCustomList का उपयोग करके इस Value को Excel की Custom Sort List में Add किया जाता है।
इसके बाद दो अलग-अलग Sheets (Sheet28 और Sheet29) में मौजूद Pivot Tables पर Sorting Apply की गई है।
PivotTables("PivotTable1") और PivotTables("PivotTable2") के जरिए Pivot Table Select किया गया है।
PivotFields("[PQuery1].[Currency].[Currency]") उस Field को Define करता है जिस पर Sort लगाना है।
AutoSort xlAscending से Currency Field को Ascending Order में Sort किया गया है, जिसमें Custom List का भी ध्यान रखा जाता है।
इस Macro की मदद से Pivot Table में Currency जैसे Fields को Standard और Consistent Order में दिखाया जा सकता है।
यह Code Excel VBA Custom Sort, Pivot Table Sorting, Currency Pivot Automation, Advanced Excel VBA जैसे SEO Keywords के लिए बहुत उपयोगी है और Reporting को Professional बनाता है।
Add fields into pivot table
Sub AddFieldsInPivot()
Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField
Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)
With thisPivot
Set ptField = .PivotFields("Gender")
ptField.Orientation = xlRowField
ptField.Position = 1
Set ptField = .PivotFields("LastName")
ptField.Orientation = xlRowField
ptField.Position = 2
Set ptField = .PivotFields("ShirtSize")
ptField.Orientation = xlColumnField
ptField.Position = 1
Set ptField = .AddDataField(.PivotFields("Cost"), "Sum of Cost", xlSum)
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
End Sub
Code explanation:
यह VBA Macro – AddFieldsInPivot Excel में Pivot Table में Fields Automatically Add और Arrange करने के लिए उपयोग किया जाता है। यह code तब बहुत काम आता है जब आपको बार-बार Pivot Table का Layout Manual बदलना पड़ता है।
सबसे पहले Macro में Pivot Table और Worksheet को Define किया गया है।
SheetNameWithPivotTable उस Sheet का नाम है जहाँ Pivot Table मौजूद है।
ptSheet.PivotTables(1) से पहली Pivot Table को Select किया गया है।
With thisPivot Block के अंदर अलग-अलग Fields को Pivot Table में Set किया गया है।
सबसे पहले Gender Field को xlRowField में रखा गया है और Position 1 दी गई है।
इसके बाद LastName Field को Row Area में Position 2 पर रखा गया है।
फिर ShirtSize Field को xlColumnField में Add किया गया है, जिससे Pivot Table में Columns के रूप में दिखे।
इसके बाद Cost Field को Data Area में Add किया गया है और उस पर Sum (xlSum) Apply किया गया है, जिससे Total Cost दिखाई देता है।
InGridDropZones = True और RowAxisLayout xlTabularRow से Pivot Table का Layout साफ और Tabular Format में दिखता है।
यह Macro Excel VBA Pivot Automation, Add Pivot Fields Automatically, Pivot Table Layout VBA, Advanced Excel Reporting जैसे SEO Keywords के लिए बहुत उपयोगी है और Report को Professional बनाता है।
Add or remove pivot calculated field without loop
Sub UpdatePivotWithOutLoop()
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim Forecast As String
Dim ThisYear As String
Dim PreYear As String
ThisYear = Left(Sheet11.Range("E6"), 4)
PreYear = Left(Sheet11.Range("F6"), 4)
Forecast = Sheet7.Range("E16")
Set pt = Sheet25.PivotTables("PivotTable1")
'remove all old fields
On Error Resume Next
For Each df In pt.DataFields
If Right(df.SourceName, 3) = "TCK" Then
With df
.Orientation = xlHidden
End With
Else
With df
.Parent.PivotItems(.Name).Visible = False
End With
End If
If Err.Number = 438 Or Err.Number = 1004 Then
pt.PivotFields(df).Orientation = xlHidden
End If
Next df
On Error GoTo 0
'add ISO to value
pt.AddDataField pt.PivotFields(ThisYear & " ISO"), " " & ThisYear & " ISO", xlSum
pt.AddDataField pt.PivotFields(PreYear & " ISO"), " " & PreYear & " ISO", xlSum
'add Actuals to value
pt.AddDataField pt.PivotFields(ThisYear & "A"), " " & ThisYear & "A", xlSum
pt.AddDataField pt.PivotFields(PreYear & "A"), " " & PreYear & "A", xlSum
'add PreYear Actuals to value
pt.AddDataField pt.PivotFields(Left(PreYear, 2) & "12A"), " " & Left(PreYear, 2) & "12A", xlSum
'add Forecast to value
pt.AddDataField pt.PivotFields(Left(ThisYear, 2) & "12" & Forecast), " " & Left(ThisYear, 2) & "12" & Forecast, xlSum
'add Target to value
pt.AddDataField pt.PivotFields(Left(ThisYear, 2) & "12TCK"), " " & Left(ThisYear, 2) & "12TCK", xlSum
'change fields position
pt.DataFields(" " & ThisYear & " ISO").Position = 1
pt.DataFields(" " & PreYear & " ISO").Position = 2
pt.DataFields(" " & ThisYear & "A").Position = 3
pt.DataFields(" " & PreYear & "A").Position = 4
pt.DataFields(" " & Left(PreYear, 2) & "12A").Position = 5
pt.DataFields(" " & Left(ThisYear, 2) & "12" & Forecast).Position = 6
pt.DataFields(" " & Left(ThisYear, 2) & "12TCK").Position = 7
Set pt = Nothing
End Sub
Code explanation:
यह VBA Macro – UpdatePivotWithOutLoop Excel में Pivot Table को Automatically Update और Rebuild करने के लिए उपयोग किया जाता है, बिना किसी Complex Loop के। यह Macro खासतौर पर Year-based Reporting, Forecast और Target Analysis के लिए बहुत उपयोगी है।
सबसे पहले Macro में कुछ Dynamic Variables Declare किए गए हैं जैसे ThisYear, PreYear और Forecast। ये Values अलग-अलग Sheets से ली जाती हैं, जिससे Pivot Table हर साल अपने-आप Update हो जाता है।
इसके बाद Set pt = Sheet25.PivotTables("PivotTable1") से Pivot Table को Select किया गया है।
On Error Resume Next के साथ पुराने Data Fields को हटाया जाता है, ताकि पहले से मौजूद गलत या Duplicate Fields Pivot Table में न रहें।
फिर Macro अलग-अलग प्रकार के Fields को Value Area में Add करता है:
- Current Year और Previous Year के ISO Values
- Current और Previous Year के Actuals
- Previous Year का 12 Month Actual
- Current Year का Forecast
- Current Year का Target (TCK)
सभी Fields को xlSum के साथ जोड़ा गया है, जिससे Total Values दिखाई दें।
अंत में Data Fields की Position Set की जाती है, ताकि Pivot Table का Order साफ और Professional दिखे।
यह Macro Excel VBA Pivot Automation, Dynamic Year Pivot, Forecast vs Actual Report, Advanced Excel VBA जैसे SEO Keywords के लिए बहुत उपयोगी है और Manual मेहनत को काफी कम करता है।
Add or remove pivot calculated field with loop
Sub UpdatePivotWithLoop()
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim LastISOYear As String
Dim LastISOMonth As String
Dim LastISO As String
Dim DFLast As String
Dim lkpISO As String
Dim Forecast As String
Dim ThisYear As String
Dim PreYear As String
ThisYear = Left(Sheet11.Range("E6"), 4)
PreYear = Left(Sheet11.Range("F6"), 4)
Forecast = Sheet7.Range("E16")
LastISOYear = Left(ThisYear, 2) - 2
LastISOMonth = Right(ThisYear, 2)
LastISO = LastISOYear & LastISOMonth & " ISO"
lkpISO = ThisYear & " ISO"
Set pt = Sheet30.PivotTables("PivotTable1")
'remove all old fields
On Error Resume Next
For Each df In pt.DataFields
With df
.Parent.PivotItems(.Name).Visible = False
End With
If Err.Number = 438 Or Err.Number = 1004 Then
DFLast = df
With df
.Orientation = xlHidden
End With
pt.PivotFields(df).Orientation = xlHidden
End If
Next df
On Error GoTo 0
'add ISO to value
Do Until pt.DataFields(pt.DataFields.Count).Caption = " " & LastISO
pt.AddDataField pt.PivotFields(lkpISO), " " & lkpISO, xlSum
lkpISO = Application.WorksheetFunction.VLookup(lkpISO, Sheets("Matrix").Range("I:J"), 2, 0)
If pt.DataFields(pt.DataFields.Count) < 3 Then
On Error Resume Next
With pt.DataFields(DFLast)
.Parent.PivotItems(.Name).Visible = False
End With
On Error GoTo 0
End If
Loop
Set pt = Nothing
End Sub
Code explanation:
यह VBA Macro – UpdatePivotWithLoop Excel में Pivot Table को Dynamic तरीके से Update करने के लिए बनाया गया है, जहाँ ISO Periods, Forecast और Year Logic का उपयोग किया गया है। यह Macro खासतौर पर तब उपयोगी होता है जब Pivot Table में Multiple Time-based Data Fields को Step-by-Step जोड़ना हो।
सबसे पहले Macro में Current Year, Previous Year और Forecast की Values अलग-अलग Sheets से ली जाती हैं। इसके बाद Last ISO Year और Month निकालकर LastISO बनाया जाता है, जिससे Loop को पता चलता है कि कहाँ तक Data जोड़ना है।
Set pt = Sheet30.PivotTables("PivotTable1") से Pivot Table को Select किया गया है।
For Each df In pt.DataFields Loop की मदद से पहले से मौजूद सभी पुराने Data Fields को Hide या Remove किया जाता है, ताकि Pivot Table साफ रहे।
इसके बाद Do Until Loop का उपयोग करके ISO Fields को एक-एक करके Value Area में Add किया जाता है।
VLookup Function की मदद से अगला ISO Period Matrix Sheet से निकाला जाता है, जिससे Loop Automatically आगे बढ़ता है।
अगर कोई पुराना या Extra Field मौजूद होता है, तो उसे फिर से Hide कर दिया जाता है, जिससे Duplicate या गलत Data न दिखे।
यह Macro Dynamic Pivot Table VBA, Loop Based Pivot Update, ISO Period Pivot Automation, Advanced Excel VBA Reporting जैसे SEO Keywords के लिए बहुत उपयोगी है और बड़े Reports को Automatically Manage करने में मदद करता है।
List all the pivot tables in a workbook
Sub ListPivotsInfor()
Dim St As Worksheet
Dim NewSt As Worksheet
Dim pt As PivotTable
Dim I, K As Long
Dim pivotCache As pivotCache
Application.ScreenUpdating = False
Set NewSt = Worksheets.Add
I = 1: K = 2
With NewSt
.Cells(I, 1) = "Name"
.Cells(I, 2) = "Source"
.Cells(I, 3) = "Refreshed by"
.Cells(I, 4) = "Refreshed"
.Cells(I, 5) = "Sheet"
.Cells(I, 6) = "Location"
On Error Resume Next
For Each St In ActiveWorkbook.Worksheets
For Each pt In St.PivotTables
Set pivotCache = pt.pivotCache
I = I + 1
.Cells(I, 1).Value = pt.Name
.Cells(I, 2).Value = pt.SourceData
If Err.Number <> 0 Then
.Cells(I, 2).Value = pivotCache.WorkbookConnection
End If
.Cells(I, 3).Value = pt.RefreshName
.Cells(I, 4).Value = pt.RefreshDate
.Cells(I, 5).Value = St.Name
.Cells(I, 6).Value = pt.TableRange1.Address
Next
Next
On Error GoTo 0
.Activate
End With
Application.ScreenUpdating = True
End Sub
Code explanation:
यह VBA Macro – ListPivotsInfor Excel Workbook में मौजूद सभी Pivot Tables की पूरी जानकारी एक नई Sheet में List करने के लिए उपयोग किया जाता है। यह Macro खासतौर पर तब बहुत काम आता है जब Workbook में कई Sheets और कई Pivot Tables हों और आपको उनका Audit या Documentation करना हो।
Macro की शुरुआत में ScreenUpdating = False किया गया है, जिससे Code तेजी से चलता है। इसके बाद एक नई Worksheet Automatically Add की जाती है, जहाँ Pivot Tables की जानकारी लिखी जाती है।
नई Sheet में Header बनाए जाते हैं जैसे:
- Pivot Table का Name
- Source Data या Connection
- Pivot किस User ने Refresh किया
- Last Refresh Date
- किस Sheet में Pivot मौजूद है
- Pivot Table का Exact Location (Cell Address)
इसके बाद Nested Loop के जरिए Workbook की हर Worksheet और उसमें मौजूद हर Pivot Table को Check किया जाता है।
pt.PivotCache से Pivot का Data Source लिया जाता है। अगर SourceData Read न हो पाए, तो Workbook Connection का नाम दिखाया जाता है।
हर Pivot Table की Detail Row-by-Row नई Sheet में Add होती जाती है। अंत में नई Sheet को Activate कर दिया जाता है।
यह Macro Excel VBA Pivot Table Audit, List All Pivot Tables, Pivot Source Tracking, Advanced Excel VBA Automation जैसे SEO Keywords के लिए बहुत उपयोगी है और बड़े Excel Reports को Manage करने में मदद करता है।
Change pivot value field based on another cell value
Option Explicit
Option Base 1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then
Call Pvt_Months
End If
End Sub
Sub Pvt_Months()
Dim pvtMonth(3) As String
Dim ListMonth As String
pvtMonth(1) = Sheet1.Range("Z2")
pvtMonth(2) = Sheet1.Range("AA2")
pvtMonth(3) = Sheet1.Range("AB2")
ListMonth = Sheet1.Range("C4")
Sheet2.PivotTables("PivotTable2").PivotFields("Sum of " & pvtMonth(1) & " YTD"). _
Orientation = xlHidden
Sheet2.PivotTables("PivotTable2").AddDataField Sheet2.PivotTables( _
"PivotTable2").PivotFields(" " & ListMonth & " YTD"), "Sum of " & ListMonth & " YTD", xlSum
Sheet3.PivotTables("PivotTable2").PivotFields("Sum of " & pvtMonth(2) & " YTD"). _
Orientation = xlHidden
Sheet3.PivotTables("PivotTable2").AddDataField Sheet3.PivotTables( _
"PivotTable2").PivotFields(" " & ListMonth & " YTD"), "Sum of " & ListMonth & " YTD", xlSum
Sheet4.PivotTables("PivotTable2").PivotFields("Sum of " & pvtMonth(3) & " YTD"). _
Orientation = xlHidden
Sheet4.PivotTables("PivotTable2").AddDataField Sheet4.PivotTables( _
"PivotTable2").PivotFields(" " & ListMonth & " YTD"), "Sum of " & ListMonth & " YTD", xlSum
End Sub
Code explanation:
यह Excel VBA Macro – Pvt_Months (Worksheet_Change Event) Pivot Table को Automatically Month के आधार पर Update करने के लिए बनाया गया है। यह Code तब Run होता है जब User किसी खास Cell में Value बदलता है, जिससे Reporting पूरी तरह Dynamic और Interactive बन जाती है।
Worksheet_Change Event का उपयोग किया गया है, जो यह Check करता है कि अगर Cell C4 में कोई Change हो तो Pvt_Months Macro Automatically Call हो जाए। इससे User को Button या Manual Refresh की जरूरत नहीं रहती।
Pvt_Months Macro में तीन अलग-अलग Previous Months को Array (pvtMonth) में Store किया गया है, जो Sheet1 के Cells Z2, AA2 और AB2 से लिए जाते हैं। वहीं ListMonth Variable में Cell C4 से Selected Month लिया जाता है।
इसके बाद Sheet2, Sheet3 और Sheet4 में मौजूद PivotTable2 को Update किया जाता है।
पुराने Month के YTD (Year To Date) Data Fields को xlHidden से Hide कर दिया जाता है और Selected Month का नया YTD Field AddDataField के जरिए Pivot Table में Add किया जाता है।
इस Macro की मदद से जैसे ही Month बदला जाता है, सभी Pivot Tables Auto Update हो जाते हैं।
यह Code Excel VBA Event Macro, Dynamic Pivot Table Month Selection, YTD Pivot Automation, Advanced Excel VBA Reporting जैसे SEO Keywords के लिए बहुत उपयोगी है और Dashboard को Smart बनाता है।
Remove filters from Pivot and range together
Sub RemoveFilter()
' Remove Worksheet filter
With Sheet5
On Error Resume Next
.ShowAllData ' Clears Advanced Filter results
On Error GoTo 0
.Rows.Hidden = False
If .AutoFilterMode Then .AutoFilterMode = False
.Range("AD1").AutoFilter
End With
End Sub
Code explanation:
यह VBA Macro – RemoveFilter Excel Worksheet से सभी Filters और Hidden Rows को Automatically Remove करने के लिए उपयोग किया जाता है। यह Macro खासतौर पर तब बहुत काम आता है जब Sheet पर Advanced Filter, AutoFilter या Hidden Rows लगे हों और आपको पूरा Data फिर से Visible करना हो।
Macro में With Sheet5 का उपयोग करके उस Worksheet को Select किया गया है जिस पर Filter हटाना है।
On Error Resume Next इसलिए लगाया गया है ताकि अगर Sheet पर कोई Filter मौजूद न हो, तब भी Code Error न दे।
.ShowAllData Command से Advanced Filter या Existing Filter Results को Clear किया जाता है। इसके बाद .Rows.Hidden = False से सभी छिपी हुई Rows को फिर से Unhide कर दिया जाता है, जिससे पूरा Data दिखाई देने लगता है।
If .AutoFilterMode Then .AutoFilterMode = False का उपयोग करके पहले से लगे हुए AutoFilter को पूरी तरह बंद कर दिया जाता है।
अंत में .Range("AD1").AutoFilter से फिर से AutoFilter Apply किया जाता है, ताकि User नए सिरे से Filter लगा सके।
यह Macro Excel VBA Remove Filter, Clear AutoFilter, Unhide Rows VBA, Excel Data Cleanup Automation जैसे SEO Keywords के लिए बहुत उपयोगी है और बड़े Data Sheets को Reset करने में मदद करता है।