Create a new dynamic range while updating pivot tables

h380am

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

I have a woorkbook with 6 pivot tables, each pivot table has its own data source.

I want a macro that creates a new dynamic range while updating pivot tables. Otherwise, a macro that checks the last column and last row with data to create a dynamic range by using cell A1 from the data source worksheet (the number of rows changes with each update of data source)
VBA Code:
[CODE=vba]Sub refreshallpivottables()

Dim ws As Worksheet
Dim pt As PivotTable

For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
             pt.RefreshTable
Next pt
Next ws

End Sub
[/CODE]

what should I add to this code ?
can someone please assist regarding this issue

Many thanks
Best regards,
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

alvisonhunter

New Member
Joined
Jun 28, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Provide us with the original range, to get the sheet's cell references and name, once we have that we can create a range and include it on this method.
 

alvisonhunter

New Member
Joined
Jun 28, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hello There,

Apologies for the delay, Due to the pandemic, I have not been able to get to my windows machine at home to try and test this code this weekend, however I came up with something for you, please give it a try and update accordingly to make sure it works as excepted. I have not tried it on Excel yet, this is basically just code from my head. I certainly hope this helps you, buddy!

VBA Code:
Sub refreshAllPivotTables()
    On Error GoTo ErrorHandler
        Dim ws As Worksheet
        Dim pt As PivotTable
        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
        ' Deletes the existing named range so that we can redefine it
            ActiveWorkbook.Names("EUROMAR-I").Delete
        ' Recreates the named range based on the PT new values on cells
        ' Do not forget to change the name here, for the name of the sheet
        ' where this range is taking place, this is crucial to make it work
            Range("'YourSheetName'!A$1:$S$" & lngLastRow).Name = "EUROMAR-I"
            MsgBox "This should be all set now."
            Exit Sub
    ErrorHandler:
    ' let's inform the user what happened describing the error in a MessageBox
    MsgBox "The following error occurred while updating your Range: " & Err.Description
    Exit Sub
End Sub
 

h380am

New Member
Joined
Apr 25, 2020
Messages
14
Office Version
  1. 365
Dear @alvisonhunter
Thanks a lot for your time and your kind assistance

1- I've tried your code on EUROMAR-I sheet and I had the msg box in attached screentshot
2- I forgot to mention that I want to creat a code that updates all pivot tables at the same time, is it doable ?

Best regards,
 

Attachments

  • Capture VBA.JPG
    Capture VBA.JPG
    24.2 KB · Views: 1

alvisonhunter

New Member
Joined
Jun 28, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Dear @alvisonhunter
Thanks a lot for your time and your kind assistance

1- I've tried your code on EUROMAR-I sheet and I had the msg box in attached screentshot
2- I forgot to mention that I want to creat a code that updates all pivot tables at the same time, is it doable ?

Best regards,
Hello

The thing is that you also needed to update the named range "name" to the one that you have on your workbook as well as the name of your sheet, I basically gave you the code for you to adjust it since I have no idea of the names that you have for both the named range and the worksheet. I am not entirely sure how to proceed with all pivots in regards to what range are they using and if this is a named range, or any other scenario, perhaps if elaborating that would've come more clear for us on this side of the earth.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,647
Messages
5,549,174
Members
410,903
Latest member
natesreich
Top