'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Sub testFnPtSourceData()

Const COST_str_strShMain As String = "xxxxxxx"

Dim oSh As Worksheet

Dim Lng_RowsEnd As Long

Dim Lng_ClnEnd As Long

With ThisWorkbook.Sheets(COST_str_strShMain)

Lng_RowsEnd = .Columns(1).Rows(Rows.Count).End(xlUp).Row

Lng_ClnEnd = .Cells(1, Columns.Count).End(xlToLeft).Column

End With

With ThisWorkbook

For Each oSh In .Sheets

If oSh.Name <> COST_str_strShMain Then

Источник_сводных = FnPtSourceData(strShMain:=COST_str_strShMain, iNbRowStart:=1, Lng_RowsEnd:=Lng_RowsEnd, iNbClnStart:=1, iNbClnEnd:=Lng_ClnEnd, strShPt:=oSh.Name)

' Источник_сводных = FnPtSourceData("Smp99_Donnees", 1, Lng_RowsEnd, 1, Lng_ClnEnd, "%")

End If

Next

End With

End Sub

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

'##### источник сводных

Function FnPtSourceData(ByVal strShMain As String, _

ByVal iNbRowStart As Integer, ByVal Lng_RowsEnd As Long, _

ByVal iNbClnStart As Integer, ByVal iNbClnEnd As Integer, _

ByVal strShPt As String) As Boolean

'strShMain - лист источник

'strShPt - лист со сводными

Dim Pt As PivotTable

With ThisWorkbook

Debug.Print strShPt, "================================================"

For Each Pt In .Sheets(strShPt).PivotTables

With Pt

' Debug.Print " ", strShPt, Pt.Name, Pt.CacheIndex

.SaveData = False 'сохранять данные

.PivotCache.RefreshOnFileOpen = False 'обновить при открытии

' pt.SourceData = strShMain & "!R1C1:R" & iNbRowName & "C" & Lng_ClnEnd

.SourceData = strShMain & "!R" & iNbRowStart & "C" & iNbClnStart & ":R" & Lng_RowsEnd & "C" & iNbClnEnd

.RefreshTable

End With 'pt

Next Pt

End With

End Function