Code to refresh Pivot table with month

[Veeru106]

Hi,

I am working on one Code which should update my pivot data in pivot and then also change month from previous one to current one...but I am getting one error not sure why?

Can anyone look into this please....

Sub vbax_60020_PT_update()
  
    Dim NewSource As String
    Dim FilterMonth
  
    With Worksheets("Raw")
        NewSource = .Name & "!" & .Cells(1).CurrentRegion.Address(ReferenceStyle:=xlR1C1)
        FilterMonth = Application.Max(.Columns(1))
    End With
  
    With Worksheets("Pivot").PivotTables("PivotTable1")
        .ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewSource)
        .RefreshTable
        .PivotFields("Month").CurrentPage = FilterMonth
    End With
  
End Sub

 

[Veeru106]

Attaching the file as well....

 

[Chihiro]

Your pivot table is actually named "PivotTable2".

If you have only 1 pivot table in the sheet, just use index# (i.e. 1) instead of the name.

Also, you are using Columns(1) in setting FilterMonth. It should be Columns(5).

So code should look like...

Option Explicit

Sub vbax_60020_PT_update()
   
    Dim NewSource As String
    Dim FilterMonth
   
    With Worksheets("Raw")
        NewSource = .Name & "!" & .Cells(1).CurrentRegion.Address(ReferenceStyle:=xlR1C1)
        FilterMonth = Application.Max(.Columns(5))
    End With
   
    With Worksheets("Pivot").PivotTables(1)
        .ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewSource)
        .RefreshTable
        .PivotFields("Month").CurrentPage = FilterMonth
    End With
   
End Sub

 

[Veeru106]

Great that is working ...Thanks a lot....Just one thing more how did we find which pivot table it is...like pivot table 1 or 2??

 

[Chihiro]

There's couple of ways to do it.

1. Click anywhere in pivot table. Go to PivotTable Tools ribbon. Top left you should see PivotTable Name.


2. Use following code to list all pivot table and parent sheet name.

Sub Demo()
Dim ws As Worksheet
Dim pvt As PivotTable

For Each ws In ThisWorkbook.Worksheets
    For Each pvt In ws.PivotTables
        Debug.Print ws.Name & " has " & pvt.Name
    Next
Next
End Sub

 

[Veeru106]

Hi..
Thanks for clarification..

I was trying same code on another sheet but it not working there.not sure why

Would you please look into this...file attached

currently it is on month 4 ,,I have updated month 5...it is refreshing but month is not picking up and also making current selection month as 0

 

[Chihiro]

That's because Trade_Month is stored as text.

You can either fix the range in sheet. Or use something like below.

Option Explicit

Sub vbax_60020_PT_update()
 
    Dim NewSource As String
    Dim FilterMonth
    Dim i As Long
 
    With Worksheets("Block Trades")
        NewSource = .Name & "!" & .Cells(1).CurrentRegion.Address(ReferenceStyle:=xlR1C1)
        FilterMonth = .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
        For i = LBound(FilterMonth) To UBound(FilterMonth)
            FilterMonth(i, 1) = CLng(FilterMonth(i, 1))
        Next
        FilterMonth = Application.Max(Application.Index(FilterMonth, , 1))
    End With
 
    With Worksheets("Pivot").PivotTables(1)
        .ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewSource)
        .RefreshTable
        .PivotFields("TRADE_MONTH").CurrentPage = FilterMonth
    End With
 
End Sub

 

[Veeru106]

Hi,

Matter still not solved...not sure how to fix the range when new data will entered every month.
above code gives me error Application defined or object defined error on Trade month

 

[Chihiro]

Hmm? Hard to say without looking at your actual workbook structure.

The code worked fine on your sample workbook. Though I've added single quote in front of and after worksheet name (since you had space in it).

See attached.

 

[Veeru106]

ok I think I need to figure it out now....thanks anyway