PivotTable not Refreshing

[jalrs]

Hello guys,

As the title suggests, my pivot table is not refreshing. I tried two methods but none seems to be working.
1st method was to add a line of

ThisWorkbook.RefreshAll

to my code. Highlighted as a comment
2nd method was to create a private sub on the template, so when the source sheet data changed, pivot table would refresh, but it didn't work also. Note that I added this private sub to the Pivot Table Worksheet and not to a new module.

Private sub is as follows:

Option Explicit
Private sub Worksheet_Change (ByVal Target as Range)

    ThisWorkbook.RefreshAll

End sub

My code is as follows:

Option Explicit
Sub filtromacro2()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim lr1 As Long, lr2 As Long, lr3 As Long, lr4 As Long, i As Long
Dim mypath As String, docname As String, valorfiltro As String

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Stock")
Set ws2 = wb1.Worksheets("MACRO 2")

lr1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

ws2.Activate

    For i = 2 To lr2
       
        valorfiltro = Cells(i, 1).Value
       
        Workbooks.Open Filename:=ThisWorkbook.Path & "\Temp\ST_TEMPLATE_" & Cells(i, 1).Value & ".xlsx"
       
        Set wb2 = Workbooks("ST_TEMPLATE_" & valorfiltro & ".xlsx")
       
        Set ws3 = wb2.Worksheets("Pendentes")
       
        ws3.Activate
       
        ws3.UsedRange.Offset(1).ClearContents
       
        lr3 = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
       
        ws1.Activate
       
        With ws1.Range("A5:AV" & lr1)
       
            .AutoFilter 46, valorfiltro
            .AutoFilter 47, "Em tratamento"
           
            With ws1
       
            .Range("A6:T" & lr1).Copy
            ws3.Cells(2, 1).PasteSpecial Paste:=xlPasteValues
           
            .Range("W6:AC" & lr1).Copy
            ws3.Cells(2, 21).PasteSpecial Paste:=xlPasteValues
           
            .Range("AF6:AV" & lr1).Copy
            ws3.Cells(2, 28).PasteSpecial Paste:=xlPasteValues
           
            .Range("BH6:BH" & lr1).Copy
            ws3.Cells(2, 45).PasteSpecial Paste:=xlPasteValues
           
            End With
           
            Application.CutCopyMode = False
           
            .AutoFilter
       
        End With
       
        lr3 = ws3.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
        ws3.Range("A" & lr3 & ":A1001").EntireRow.Delete
       
        wb2.Activate
       
        ws3.Activate
       
        lr4 = Cells(Rows.Count, "AP").End(xlUp).Row
       
        If lr4 > 1 Then
       
            Range("AU2:AU" & lr4).FormulaR1C1 = _
            "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-46]:C[-45],2,0))"
           
        End If
       
        ws3.Protect Password:="blabla", _
        DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        UserInterfaceOnly:=True, _
        AllowFormattingCells:=False, _
        AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, _
        AllowInsertingColumns:=False, _
        AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, _
        AllowDeletingColumns:=False, _
        AllowDeletingRows:=False, _
        AllowSorting:=True, _
        AllowFiltering:=False, _
        AllowUsingPivotTables:=False

        mypath = ThisWorkbook.Path & "\Anexos\"
           
        wb1.Activate
       
        ws2.Activate
       
        docname = Cells(i, 5).Value
       
        wb2.Activate
       
        ws3.Activate

        ThisWorkbook.RefreshAll 'added here
       
        ActiveWorkbook.SaveAs Filename:=mypath & docname & ".xlsx", FileFormat:=xlOpenXMLWorkbook
   
        ActiveWorkbook.Close
       
    Next i
   
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Any Help is appreciated,

Thanks

 

[jalrs]

I am not following what you mean.
Have you given the Table a meaningfull name ?
Have you used that name in the Data Source of your Pivot Table ?

View attachment 66315

Yes yes, I caught up with you. We are on the same page.
Now, since when I do CTRL+T I need to assign a range. I can't assign from A1:AY, it displays an error. So I assigned A1:AY1000. Therefore I don't see how it is dynamic.
Since my headers are on row 1 I checked the box regarding those and assigned the range from A1 instead of A2. Is this correct?

Thoughts?

Thanks

 

[Alex Blakenburg]

I tried to find a quick video that you would show you the benefit of using a table.
Here is the shortest I could find at just over 2mins.
At the 54 sec mark the auto expansion benefits are discussed in the context of a chart's data source. You can effectively change the word Chart to Pivot Table for your purposes.

It may not work for you only because you are using the sheet as a "Template" but have a look at the video and then come back to me.
I used to use a Named Range as my Data Source for the Pivot Table(s) and then used a macro to resize the Named Range but the Auto Expanding feature of the Excel Table pretty much does the same thing without needing a macro.

 

[jalrs]

I tried to find a quick video that you would show you the benefit of using a table.
Here is the shortest I could find at just over 2mins.
At the 54 sec mark the auto expansion benefits are discussed in the context of a chart's data source. You can effectively change the word Chart to Pivot Table for your purposes.

It may not work for you only because you are using the sheet as a "Template" but have a look at the video and then come back to me.
I used to use a Named Range as my Data Source for the Pivot Table(s) and then used a macro to resize the Named Range but the Auto Expanding feature of the Excel Table pretty much does the same thing without needing a macro.

Appreciate the video and I will have a look at it and get back to you as soon as possible.

Thank you Alex!

 

[Alex Blakenburg]

Login off for the night.

 

[jalrs]

Login off for the night.

Good night Alex.

See you tomorrow.

Thanks for the support!

 

[jalrs]

Unless you want to go the VBA route, the easiest way to make your source dynamic is to convert your data into an Excel Table.
This will generate a table name (which you should change to something more meaningful) then use that table name as your data source in the pivot.

To convert the data to a table

• Select any cell in your data range
• Then either
  • Insert > Table (3rd button from the left )
  • OR Ctrl + T
• Then Click on the tab Table Design and in the white box on the far left put in a more meaningful table name.
  (I prefix mine with tbl so that in name manager they sort together and in a formula you can start typing tbl and is goes to your tables)
• The copy that name and paste it into the data source box of your pivot

Hey Alex,

I managed to do it by your suggested method above, thank you very much!
Liked it as I can't mark it as solution because we already have one.

Thank you Alex!

 

[Alex Blakenburg]

No problem. Glad you found it useful.