PDA

View Full Version : [SOLVED] How to prevent a Pivot Table from Re-Sizing



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

Bob Phillips
11-30-2016, 02:42 AM
Just set the autoformat property


ActiveSheet.PivotTables("PivotTable1").HasAutoFormat = False

Poundland
11-30-2016, 05:47 AM
Xld,

Thanks for the response, I tried adding this code into the Pivot Refresh but the Pivot Table still wanted to change size during the refresh operation.

I have added a few code lines to add in blank columns before the Pivot is refreshed and then to remove them again after and this has solved my problem albeit in a rather clumsy way.

Aflatoon
12-01-2016, 06:44 AM
Have you tried setting the ManualUpdate property to True while you are making the changes and then back to False afterwards?