MyTetra Share
Делитесь знаниями!
SourceData
20.12.2016
11:44
Текстовые метки: SourceData, сводные, pt
Раздел: VBA - Сводные


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

Sub testFnPtSourceData()

With ThisWorkbook.Sheets("Smp99_Donnees")

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

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

End With

Источник_сводных = FnPtSourceData("Smp99_Donnees", 1, iNbRowEnd, 1, iNbCol, "Pv")

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

End Sub


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

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

Function FnPtSourceData(ByVal strShMain As String, _

ByVal iNbRowStart As Integer, ByVal iNbRowEnd As Long, _

ByVal iNbClnStart As Integer, ByVal iNbClnEnd As Integer, _

ByVal strShPt As String) As Boolean

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

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

With ThisWorkbook

For Each pt In .Sheets(strShPt).PivotTables

With pt

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

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

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

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

.RefreshTable

End With 'pt

Next pt


End With

End Function


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

Function FnPtSourceData(ByVal strShMain As String, ByVal strShPt As String)

With ThisWorkbook

iClnName = FnFindCnlINRow(strShMain, "Материал", 1, 1)

iNbRowName = .Sheets(strShMain).Columns(iClnName).Rows(65536).End(xlUp).Row

iNbCol = .Sheets(strShMain).Cells(1, 256).End(xlToLeft).Column


For Each pt In .Sheets(strShPt).PivotTables

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

pt.RefreshTable

Next pt


End With

End Function


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

Range("GY11").Select

ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà5").SaveData = False

ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà5").PivotCache.RefreshOnFileOpen = True

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