Adapted from [Dynamically Change A Pivot Table's Data Source Range With This VBA Macro Code][1]:

1. The <code>[PivotTable][pt].[SourceData][sd]</code> property can be set  which is set via the [`ChangePivotCache`][cpc] method.
2. To create a new [`PivotCache`][pc], call <code>[ActiveWorkbook][ab].[PivotCaches][pcs].[Create][c]</code>
3. You'll need to pass in a `SourceType` and a [`Range`][r] as `SourceData`. 
4. Finally, once updated, make sure to call [`RefreshTable`][rt] to apply the changes.

Here's an example that will automatically find every pivot table in your workbook and update it.




    Sub AdjustPivotDataRange()
        Dim pt As PivotTable, pc As PivotCache
        Dim dataSheet As Worksheet, ws As Worksheet
        Dim startPoint As Range, dataSource As Range, newRange As String
    
        ' get worksheet with data
        Set dataSheet = ThisWorkbook.Worksheets("Sheet1")
    
        ' Dynamically Retrieve Range Address of Data
        Set startPoint = dataSheet.Range("A1")
        Set dataSource = dataSheet.Range(startPoint, startPoint.SpecialCells(xlLastCell))
        newRange = dataSheet.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1)
        
        ' create new PivotCache
        Set pc = ActiveWorkbook.PivotCaches.Create( _
                   SourceType:=xlDatabase, _
                   SourceData:=newRange)
    
        ' loop through all tables in all sheets
        For Each ws In ActiveWorkbook.Worksheets
            For Each pt In ws.PivotTables
    
                ' update pivot source and refresh
                pt.ChangePivotCache pc
                pt.RefreshTable
    
            Next pt
        Next ws
    
    End Sub

Just replace `"Sheet1"` with wherever your datasource is located.  
[1]: http://www.thespreadsheetguru.com/the-code-vault/2014/7/9/change-a-pivot-tables-data-source-range
[pt]:  https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotTable
[sd]:  https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotTable.SourceData
[pc]:  https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotCache
[cpc]: https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotTable.ChangePivotCache
[ab]:  https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.ActiveWorkbook
[pcs]: https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotCaches
[c]:   https://learn.microsoft.com/en-us/office/vba/api/excel.pivotcaches.create
[rt]:  https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotTable.RefreshTable
[r]:   https://learn.microsoft.com/en-us/office/vba/api/Excel.Range(object)

Is there an easy way to update the data source for multiple pivot tables on a single Excel sheet at the same time?

All of the pivot tables reference the same named range, but I need to create a second worksheet that has the same pivot tables, but accessing a different named range.

Ideally I would like to be able to do some kind of search and replace operation (like you can do on formulae), rather than updating each individual pivot table by hand.

Any suggestions?