MyTetra Share
Делитесь знаниями!
SourceData
Время создания: 12.10.2019 20:12
Текстовые метки: SourceData, сводные, pt
Раздел: !Закладки - VBA - Excel - Сводные
Запись: adgaver/mytetra_base_New/master/base/1482223499gztptcworu/text.html на raw.githubusercontent.com

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

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



Так же в этом разделе:
 
MyTetra Share v.0.55
Яндекс индекс цитирования