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

 

[Alex Blakenburg]

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

 

[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

Thanks for the reply Alex,

I'll try this approach first and see if I succeed with it. If not, I will comeback here.

Thank you very much

 

[Alex Blakenburg]

Sounds good let me know.

 

[jalrs]

Sounds good let me know.

I'm failing as I try to do the CTRL+T thing.
It's overwritting my last columns of data with the table

 

[jalrs]

Hey @Alex Blakenburg ,

Given my post #24, could we get a vba workaround as you also suggested?
Attached I show you the issue regarding post #24

Thanks Alex

 

[Alex Blakenburg]

I'm failing as I try to do the CTRL+T thing.
It's overwritting my last columns of data with the table

Tackling the Table issue - what do you mean it is overwritting the last columns ?
The table conversion shouldn't "overwrite" anything. The only thing that might look a bit like that is if you have merged cells.

 

[jalrs]

Tackling the Table issue - what do you mean it is overwritting the last columns ?
The table conversion shouldn't "overwrite" anything. The only thing that might look a bit like that is if you have merged cells.

With overwritting I mean that, when I did CTRL+T and pressed Enter, my last 4 columns got into a table format, and lost their original template like the columns before them.
You can see the difference on the attachment on post #25.

Thanks Alex

 

[Alex Blakenburg]

What happens is you then go into Table Design > Table Styles > at the very bottom click on Clear

 

[jalrs]

What happens is you then go into Table Design > Table Styles > at the very bottom click on Clear

It worked. I still have the isblank rows displaying, but I believe that is regarding my other open thread.
Question, I went and checked my data source was indeed the table I did, but I can't find the table on any part of the sheet.

Is it supposed to be like that?

Thanks Alex

 

[Alex Blakenburg]

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 ?