Poundland
11-29-2016, 04:30 AM
All,
I have some code, below, that I have written, the code works well apart from one issue.
I have formulas in the columns next to the Pivot table that are being overwritten after each Pivot Table refresh occurs.
The Pivot Table once completely refreshed occupies that same number of columns each time, but the process of refreshing the data is causing the overwrite.
My question is, how can I get the Pivot Table to refresh to the correct data without having to expand each time?
The Pivot Table is an OLAP query, and each complete refresh will only occupy the same space.
my Code
Sub Pivot()
Select Case VBThisYear
Case Is = 1 ' Monday's report (Full Weeks data) ' ************ THIS WORKS ******************
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Fiscal Week]").VisibleItemsList = Array("")
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Fiscal Week]").VisibleItemsList = Array( _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strF2YR & "].&[" & strFQ & "].&[" & strFW & "]", _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFLY & "].&[" & strFQ & "].&[" & strFW & "]", _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFLW & "]", _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFW & "]")
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Product].[SKU-MSKU-Class-Department].[Master Article]").VisibleItemsList = _
Array( _
"[Product].[SKU-MSKU-Class-Department].[Department].&[" & rngdept.Value & "].&[" & rngsd.Value & "].&[" & rngclass.Value & "].&[" & rngSC.Value & "].&[" & strMSKU & "]" _
)
Case Is = 2 ' Tuesday's report (1 Days Data) ' ************ THIS WORKS ******************
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Fiscal Week]").VisibleItemsList = Array("")
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Date]").VisibleItemsList = Array( _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strF2YR & "].&[" & strFQ & "].&[" & strFW & "].&[" & Year2YrTW(7) & "-" & Month2YrTW(7) & "-" & Day2YrTW(7) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFLW & "].&[" & YearLW(7) & "-" & MonthLW(7) & "-" & DayLW(7) & "T00:00:00]" _
, _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFLY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearLYTW(7) & "-" & MonthLYTW(7) & "-" & DayLYTW(7) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearTW(7) & "-" & MonthTW(7) & "-" & DayTW(7) & "T00:00:00]" _
)
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Product].[SKU-MSKU-Class-Department].[Master Article]").VisibleItemsList = _
Array( _
"[Product].[SKU-MSKU-Class-Department].[Department].&[" & rngdept.Value & "].&[" & rngsd.Value & "].&[" & rngclass.Value & "].&[" & rngSC.Value & "].&[" & strMSKU & "]" _
)
Case Is = 3 ' Wednesday's Report (2 Days of data) ' ************ THIS WORKS ******************
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Fiscal Week]").VisibleItemsList = Array("")
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Date]").VisibleItemsList = Array( _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strF2YR & "].&[" & strFQ & "].&[" & strFW & "].&[" & Year2YrTW(7) & "-" & Month2YrTW(7) & "-" & Day2YrTW(7) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearTW(7) & "-" & MonthTW(7) & "-" & DayTW(7) & "T00:00:00]" _
, _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strF2YR & "].&[" & strFQ & "].&[" & strFW & "].&[" & Year2YrTW(6) & "-" & Month2YrTW(6) & "-" & Day2YrTW(6) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearTW(6) & "-" & MonthTW(6) & "-" & DayTW(6) & "T00:00:00]" _
, _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFLY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearLYTW(7) & "-" & MonthLYTW(7) & "-" & DayLYTW(7) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFLW & "].&[" & YearLW(7) & "-" & MonthLW(7) & "-" & DayLW(7) & "T00:00:00]" _
, _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFLY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearLYTW(6) & "-" & MonthLYTW(6) & "-" & DayLYTW(6) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFLW & "].&[" & YearLW(6) & "-" & MonthLW(6) & "-" & DayLW(6) & "T00:00:00]")
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Product].[SKU-MSKU-Class-Department].[Master Article]").VisibleItemsList = _
Array( _
"[Product].[SKU-MSKU-Class-Department].[Department].&[" & rngdept.Value & "].&[" & rngsd.Value & "].&[" & rngclass.Value & "].&[" & rngSC.Value & "].&[" & strMSKU & "]" _
)
End Select
End Sub
I have some code, below, that I have written, the code works well apart from one issue.
I have formulas in the columns next to the Pivot table that are being overwritten after each Pivot Table refresh occurs.
The Pivot Table once completely refreshed occupies that same number of columns each time, but the process of refreshing the data is causing the overwrite.
My question is, how can I get the Pivot Table to refresh to the correct data without having to expand each time?
The Pivot Table is an OLAP query, and each complete refresh will only occupy the same space.
my Code
Sub Pivot()
Select Case VBThisYear
Case Is = 1 ' Monday's report (Full Weeks data) ' ************ THIS WORKS ******************
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Fiscal Week]").VisibleItemsList = Array("")
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Fiscal Week]").VisibleItemsList = Array( _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strF2YR & "].&[" & strFQ & "].&[" & strFW & "]", _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFLY & "].&[" & strFQ & "].&[" & strFW & "]", _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFLW & "]", _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFW & "]")
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Product].[SKU-MSKU-Class-Department].[Master Article]").VisibleItemsList = _
Array( _
"[Product].[SKU-MSKU-Class-Department].[Department].&[" & rngdept.Value & "].&[" & rngsd.Value & "].&[" & rngclass.Value & "].&[" & rngSC.Value & "].&[" & strMSKU & "]" _
)
Case Is = 2 ' Tuesday's report (1 Days Data) ' ************ THIS WORKS ******************
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Fiscal Week]").VisibleItemsList = Array("")
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Date]").VisibleItemsList = Array( _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strF2YR & "].&[" & strFQ & "].&[" & strFW & "].&[" & Year2YrTW(7) & "-" & Month2YrTW(7) & "-" & Day2YrTW(7) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFLW & "].&[" & YearLW(7) & "-" & MonthLW(7) & "-" & DayLW(7) & "T00:00:00]" _
, _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFLY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearLYTW(7) & "-" & MonthLYTW(7) & "-" & DayLYTW(7) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearTW(7) & "-" & MonthTW(7) & "-" & DayTW(7) & "T00:00:00]" _
)
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Product].[SKU-MSKU-Class-Department].[Master Article]").VisibleItemsList = _
Array( _
"[Product].[SKU-MSKU-Class-Department].[Department].&[" & rngdept.Value & "].&[" & rngsd.Value & "].&[" & rngclass.Value & "].&[" & rngSC.Value & "].&[" & strMSKU & "]" _
)
Case Is = 3 ' Wednesday's Report (2 Days of data) ' ************ THIS WORKS ******************
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Fiscal Week]").VisibleItemsList = Array("")
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Date].[Year-Qtr-Week].[Date]").VisibleItemsList = Array( _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strF2YR & "].&[" & strFQ & "].&[" & strFW & "].&[" & Year2YrTW(7) & "-" & Month2YrTW(7) & "-" & Day2YrTW(7) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearTW(7) & "-" & MonthTW(7) & "-" & DayTW(7) & "T00:00:00]" _
, _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strF2YR & "].&[" & strFQ & "].&[" & strFW & "].&[" & Year2YrTW(6) & "-" & Month2YrTW(6) & "-" & Day2YrTW(6) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearTW(6) & "-" & MonthTW(6) & "-" & DayTW(6) & "T00:00:00]" _
, _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFLY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearLYTW(7) & "-" & MonthLYTW(7) & "-" & DayLYTW(7) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFLW & "].&[" & YearLW(7) & "-" & MonthLW(7) & "-" & DayLW(7) & "T00:00:00]" _
, _
"[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFLY & "].&[" & strFQ & "].&[" & strFW & "].&[" & YearLYTW(6) & "-" & MonthLYTW(6) & "-" & DayLYTW(6) & "T00:00:00]", "[Date].[Year-Qtr-Week].[Fiscal Year].&[" & strFY & "].&[" & strFQ & "].&[" & strFLW & "].&[" & YearLW(6) & "-" & MonthLW(6) & "-" & DayLW(6) & "T00:00:00]")
shtSCube.PivotTables("PivotTable1").PivotFields( _
"[Product].[SKU-MSKU-Class-Department].[Master Article]").VisibleItemsList = _
Array( _
"[Product].[SKU-MSKU-Class-Department].[Department].&[" & rngdept.Value & "].&[" & rngsd.Value & "].&[" & rngclass.Value & "].&[" & rngSC.Value & "].&[" & strMSKU & "]" _
)
End Select
End Sub