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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,114,648
Messages
5,549,176
Members
410,903
Latest member
natesreich
Top