Create a new dynamic range while updating pivot tables

[h380am]

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)

[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,

 

[alvisonhunter]

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.

 

[h380am]

Hello @alvisonhunter

Range => 'EUROMAR-I'!$A$1:$S$30

thank you for your help

 

[h380am]

Dear @alvisonhunter

any news please

many thanks

 

[alvisonhunter]

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!

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]

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,

 

[alvisonhunter]

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.

 

[h380am]

Hello @alvisonhunter

well noted
thank you for your kind assistance