Update used RANGE by finding the last non blank row (Pivot tables)

Status
Not open for further replies.

h380am

New Member
Joined
Apr 25, 2020
Messages
14
Office Version
  1. 365
Dear members,

I want a macro that creates a new dynamic range while updating pivot tables. Otherwise, a macro that updates the used range with the new rows added to the data source sheet then updates all pivot tables within the woorkbook.
NB: each pivot table has its own DATA SOURCE sheet

1 - is it doable ?
2- if it's doable. can someone please assist regarding this issue ?

Best regards,
VBA Code:
Sub refreshAllPivotTables()
    
        Dim ws As Worksheet
        Dim pt As PivotTable
        Dim lastrow As Long
        
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                    pt.RefreshTable
            Next pt
        Next ws
        With pt.TableRange1
            lngLastRow = .Cells(.Cells.Count).Row
        End With
    
End Sub
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,114,236
Messages
5,546,656
Members
410,753
Latest member
Ian R
Top