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]

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.

Ok Alex, good night!

I'll do it via dropbox and when you wake up you'll have it

Thanks!

 

[jalrs]

Hey Alex,

There it is share (2).zip

Additional Notes:

-Delete attachments folder files and run the macro. I didn't delete them, because I simulated on "ST_Apoio SP.xlsx" so you could see the differences between afterrunning.png and aftermanuallyrefreshin.png
-Run Macro from "Stock.xlsm" on "Readme" sheet
-Status to look on pivot table should be "Em tratamento"
-Worksheet Change private sub added to all templates (templates when populated generate a copy with a different name to attachments)
-Added ThisWorkbook.RefreshAll to macro1 module at Stock.xlsm Workbook prior to protecting the sheet as you could read on the code.

Any additional information you need, feel free to ask.

Thanks Alex.

 

[jalrs]

Hey @Alex Blakenburg ,

Have you seen it?

Thanks

 

[Alex Blakenburg]

Sorry although I got a notification for your Post #11 I didn't get one for you post #12.

1) In your template workbook, delete code the sitting under Sheet2 (Resumo)
(remove the worksheet_Change event)

2) In your main Stock Macro
Replace this

ThisWorkbook.RefreshAll

With this

wb2.RefreshAll

I assume you are then saving it as xlsx to a different folder without the word template and leaving the template intact.

Let me know how you go. I will probably be login off for the night now though

 

[Alex Blakenburg]

PS: "ThisWorkbook" is the workbook that contains the code. In your case that is the Stock workbook. That is not where the pivot is.

 

[jalrs]

Sorry although I got a notification for your Post #11 I didn't get one for you post #12.

1) In your template workbook, delete code the sitting under Sheet2 (Resumo)
(remove the worksheet_Change event)

2) In your main Stock Macro
Replace this

ThisWorkbook.RefreshAll

With this

wb2.RefreshAll

I assume you are then saving it as xlsx to a different folder without the word template and leaving the template intact.

Let me know how you go. I will probably be login off for the night now though

Hey Alex, I think that was a typo on my side, because I didn't reply nor quoted anything nor tagged you with "@" prior to your name.

Regarding the assumptions, they are correct. I filter from "stock" to a template and save the template as xlsx on a different location with a different name, keeping the template intact.

Thanks for the feedback, I'll try it and comeback to you.

Have a good night and catch you tomorrow.

 

[jalrs]

Hey @Alex Blakenburg ,

That did work indeed. Marked as solution.
Not sure about the value returned but that's on my end. Must change the data source columns, I believe.

Nonetheless, thank you so much for helping me, it was a tiny detail but I thought when I activated a workbook within the code, "ThisWorkbook" would refer to the last activated workbook.

Thanks once again and enjoy your weekend!

 

[Alex Blakenburg]

Not sure about the value returned but that's on my end. Must change the data source columns, I believe.

Pivot Data sources require less maintenance if you convert your source ranges to Excel Tables and use th Table Name as your Pivot Table's Datasource. Tables dynamically expand and contract as you add or delete rows and columns. Columns generally don't change much but the number of rows of data tends every time you get new data.
(I would encourage you to use your own table names and not the default Table1, 2 etc)

"ThisWorkbook" would refer to the last activated workbook.

The code in which the Code is held is generally the key workbook you keep coming back to so ThisWorkbook is a handy way of doing that.
ActiveWorkbook is the term for what you wanted.
Using Activate and Select statements significantly slows down the code and it also makes it difficult to keep track of what is active when your code does something. It is better to do what your code already mostly does which is to assign it to a variable eg your wb2 and use that when you do something to that workbook. I would be inclined to use more meaningful names so you don't have to trace back and figure which workbook was wb2. eg wb2,RefreshAll vs ActiveWorkbook.RefreshAll (or perhaps destwb.RefreshAll)

 

[jalrs]

Pivot Data sources require less maintenance if you convert your source ranges to Excel Tables and use th Table Name as your Pivot Table's Datasource. Tables dynamically expand and contract as you add or delete rows and columns. Columns generally don't change much but the number of rows of data tends every time you get new data.
(I would encourage you to use your own table names and not the default Table1, 2 etc)


The code in which the Code is held is generally the key workbook you keep coming back to so ThisWorkbook is a handy way of doing that.
ActiveWorkbook is the term for what you wanted.
Using Activate and Select statements significantly slows down the code and it also makes it difficult to keep track of what is active when your code does something. It is better to do what your code already mostly does which is to assign it to a variable eg your wb2 and use that when you do something to that workbook. I would be inclined to use more meaningful names so you don't have to trace back and figure which workbook was wb2. eg wb2,RefreshAll vs ActiveWorkbook.RefreshAll (or perhaps destwb.RefreshAll)

Hello Alex,

Thanks for your nice detailed explanation, I'll take those into account when making my next project.
Pleasure to learn from you!

Thanks!

 

[jalrs]

Hi again @Alex Blakenburg ,

I discovered why it's returning the wrong values. It is because my template is reading untill a certain row (eg: A2:AY27) that was specified by my boss. In order to prevent this, I tried "cheating" a way by adding the pivot data source from A2:AY, but it displays on the pivot table the "isblank" rows.

I wonder if there's a way to make the pivot table data source dynamic. From A2:LRAY being lr the lastrow with data

I didn't open a new thread because I find this question relatable. If not, please tell me and I will do so.

Thanks!