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]

Hello guys,

Up.

Thanks

 

[jalrs]

Hello guys,

Up.

Thanks

 

[jalrs]

Hello guys,

Up.

Thanks

 

[jalrs]

Hello guys,

Up.

Thanks

 

[Alex Blakenburg]

Is your pivot table on ws3 = wb2.Worksheets("Pendentes") or on another "Protected" sheet ?
You would need to unprotect the sheet with the pivot on it before you run the refresh.

 

[jalrs]

Is your pivot table on ws3 = wb2.Worksheets("Pendentes") or on another "Protected" sheet ?
You would need to unprotect the sheet with the pivot on it before you run the refresh.

Hello Alex, thanks for your reply

My pivot table is on another worksheet. I didn't declare it since the 1st method I tried covered the whole workbook (ThisWorkbook.RefreshAll) and the 2nd method I clicked on "view code" within the desired sheet and pasted that code there.

So, to sum up, the sheet with pivot table is called "Resumo" and no, it is not a protected sheet.

Any help? If anything not clear I can provide more information

Thanks for your answer Alex, appreciate it!

 

[Alex Blakenburg]

How are you changing your Pivot's data source for new data ?
Does it only appear to be not refreshing because it is not picking up new rows ?
If you change an existing row say row 1 of your data source to an amount that will be obvious in the pivot and run your refresh does it update in the pivot ?

 

[jalrs]

How are you changing your Pivot's data source for new data ?
Does it only appear to be not refreshing because it is not picking up new rows ?
If you change an existing row say row 1 of your data source to an amount that will be obvious in the pivot and run your refresh does it update in the pivot ?

1st question. Data is changed manually
2nd question. That doesn't seem the case.
3rd question. It doesn't.

Real example is: I have empty templates with pivot tables. Template is empty so is pivot table. Master sheet filters according to each template. Ideally, when new data is found on each template, the pivot table would refresh automatically.

I can provide examples if easier. How it is vs how it is when data is filtered vs how it is supposed to be.

Thanks Alex

 

[Alex Blakenburg]

Yes please.
Because of the nature of the issue, if you can share the workbook via googledrive, onedrive, Dropbox, that would help. Just make sure the problem exists in the workbook you share.
I am login off for the night, will have a look at what you send tomorrow.