Hi guys,
Once again I call upon your collective wisdom for an issue I have.
I have created a Userform that allows a user to select various elements of data to be pivoted from a database.
All works as expected apart from the part where I am populating the datafields, I am cycling through a date range, the start date of which has been selected by the user, but all columns of data in the Pivot are the same as the first selected date.
I think the Code that is the issue is below, with the full code sequence underneath;
Thank you in advance for your help..
' Variable captures the start Date to compile Pivot Data from FisDte = TextBox9' Finds the fiscal Date and assigns an object variable to it. Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Set FisCheck = Selection.Find(FisDte, ActiveCell).CellsThe full code is below;' Adds the data fields to the Pivot Table For Each cll In Data.Range(FisCheck, FisCheck.End(xlToRight)).Cells ActiveSheet.PivotTables(Department).AddDataField ActiveSheet.PivotTables( _ Department).PivotFields(FisDte), "Sum " & cll, xlSum Next cll
[Private Sub CommandButton1_Click() UserForm1.Hide 'Unload Me ' Department Variables Dim Ents As String, Diy As String, Cloth As String, Home As String, Stats As String, All As String ' Roll To variables Dim Dept As String, SubD As String, Clas As String, SubC As String, Msku As String ' Other variables Dim DestnSheet As Range, FisCheck As Range, FisDte As String, Department As String, cll As Range, Data As Worksheet Dim Sht As Worksheet ' Pivot Table Variables Dim PvtTbl As PivotTable Dim pvtFld As PivotField Application.Calculation = xlCalculationManual ' Variable captures the start Date to compile Pivot Data from FisDte = TextBox9 ' Checks to see if start date field has been populated on the User form If FisDte = "" Then MsgBox ("Fiscal Date is missing, please add a Fiscal Date to compile data from") UserForm1.Show End Else End If ' Which sheet to pull pivot data from If CheckBox20 = True Then Department = "ENTERTAINMENT (150)" Else If CheckBox21 = True Then Department = "CLOTHING (700)" Else If CheckBox22 = True Then Department = "DIY (600)" Else If CheckBox23 = True Then Department = "All Depts" Else If CheckBox24 = True Then Department = "STATIONARY (750)" Else If CheckBox26 = True Then Department = "HOMEWARES (400)" Else Department = "" End If End If End If End If End If End If ' What hierarchy to roll to If CheckBox27 = False And CheckBox29 = False And CheckBox31 = False And CheckBox32 = False And CheckBox28 = False Then MsgBox ("No Roll to selection has been made, please choose a Roll to option") UserForm1.Show End Else If CheckBox27 = True Then Dept = "Yes" Position1 = 1 If CheckBox29 = True Then SubD = "Yes" Position2 = 2 If CheckBox31 = True Then Clas = "Yes" Position3 = 3 If CheckBox32 = True Then SubC = "Yes" Position4 = 4 If CheckBox28 = True Then Msku = "Yes" Position5 = 5 Else End If End If End If End If End If End If ' Selects the required departments sheet Sheets(Department).Activate ' Assigns the department sheet to an object variable Set Data = Sheets(Department) ' Finds the fiscal Date and assigns an object variable to it. Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Set FisCheck = Selection.Find(FisDte, ActiveCell).Cells ' Creates a new sheet to place Pivot to Sheets.Add.Name = "Pivot " & Department & Format(Now, "dd.mm.yyyy") ' Sets the newly created sheet to an object variable Set DestnSheet = Sheets("Pivot " & Department & Format(Now, "dd.mm.yyyy")).Range("A3") ' Creats the Pivot Table ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ Department & "!R1C1:R20000C133", Version:=xlPivotTableVersion15). _ CreatePivotTable TableDestination:=DestnSheet, TableName:=Department _ , DefaultVersion:=xlPivotTableVersion15 ' Builds the Hierarchy for the Pivotfields If Position1 = 1 Then With ActiveSheet.PivotTables(Department).PivotFields("Department") .Orientation = xlRowField .Position = 1 End With Position6 = Position1 + 1 If Position2 = 2 Then With ActiveSheet.PivotTables(Department).PivotFields("Sub Department") .Orientation = xlRowField .Position = 2 End With Position6 = Position2 + 1 If Position3 = 3 Then With ActiveSheet.PivotTables(Department).PivotFields("Class") .Orientation = xlRowField .Position = 3 End With Position6 = Position3 + 1 If Position4 = 4 Then With ActiveSheet.PivotTables(Department).PivotFields("Sub Class") .Orientation = xlRowField .Position = 4 End With Position6 = Position4 + 1 If Position5 = 5 Then With ActiveSheet.PivotTables(Department).PivotFields("MSKU") .Orientation = xlRowField .Position = 5 End With Position6 = Position5 + 1 Else End If End If End If End If End If ' Formats the Pivot table ActiveSheet.PivotTables(Department).RowAxisLayout xlTabularRow Set PvtTbl = ActiveSheet.PivotTables(Department) 'Hides Subtotals for all fields in the PivotTable . With PvtTbl For Each pvtFld In .PivotFields pvtFld.Subtotals(1) = True pvtFld.Subtotals(1) = False Next pvtFld End With With ActiveSheet.PivotTables(Department).PivotFields("Measure") .Orientation = xlRowField .Position = Position6 End With ' Defines which measures to show from the Userform selection With ActiveSheet.PivotTables(Department).PivotFields("Measure") .PivotItems("Act/Fcasted Margin %").Visible = CheckBox53 .PivotItems("Actual / Forecasted Cash £").Visible = CheckBox36 .PivotItems("Actual / Forecasted Despatch Units").Visible = CheckBox8 .PivotItems("Actual / Forecasted Nett £").Visible = CheckBox35 .PivotItems("Actual / Forecasted Sales £").Visible = CheckBox34 .PivotItems("Actual / Forecasted Sales Units").Visible = CheckBox2 .PivotItems("DC Days Cover").Visible = CheckBox63 .PivotItems("DC Stock Units").Visible = CheckBox17 .PivotItems("Despatch Adjustment").Visible = CheckBox9 .PivotItems("Despatches Last Fiscal Yr Units").Visible = CheckBox7 .PivotItems("Lost Despatches").Visible = CheckBox6 .PivotItems("Lost Sales").Visible = CheckBox5 .PivotItems("On Order - Booked Cash £").Visible = CheckBox40 .PivotItems("On Order - Booked Margin %").Visible = CheckBox56 .PivotItems("On Order - Booked Nett £").Visible = CheckBox39 .PivotItems("On Order - Unbooked Cash £").Visible = CheckBox42 .PivotItems("On Order - UnBooked Margin %").Visible = CheckBox57 .PivotItems("On Order - Unbooked Nett £").Visible = CheckBox41 .PivotItems("On Order Qty - Booked Units").Visible = CheckBox12 .PivotItems("On Order Qty - UnBooked Units").Visible = CheckBox13 .PivotItems("Open To Buy Units").Visible = CheckBox11 End With With ActiveSheet.PivotTables(Department).PivotFields("Measure") .PivotItems("OTB - Cash £").Visible = CheckBox45 .PivotItems("OTB - Nett £").Visible = CheckBox44 .PivotItems("OTB Margin %").Visible = CheckBox59 .PivotItems("Rescheduled Margin %").Visible = CheckBox58 .PivotItems("Reschedules").Visible = CheckBox15 .PivotItems("Reschedules - Cash £").Visible = CheckBox48 .PivotItems("Reschedules - Nett £").Visible = CheckBox38 .PivotItems("Sales Adjustment +/-").Visible = CheckBox3 .PivotItems("Sales Adjustment Margin %").Visible = CheckBox54 .PivotItems("Sales Last Fiscal Yr Units").Visible = CheckBox1 .PivotItems("Store Days Cover").Visible = CheckBox64 .PivotItems("Store Stock Units").Visible = CheckBox18 .PivotItems("Suggested Receipts Volume").Visible = CheckBox14 .PivotItems("Total Adjusted Cash £").Visible = CheckBox52 .PivotItems("Total Adjusted Despatches").Visible = CheckBox10 .PivotItems("Total Adjusted Margin %").Visible = CheckBox55 .PivotItems("Total Adjusted Nett £").Visible = CheckBox33 .PivotItems("Total Adjusted Sales").Visible = CheckBox4 .PivotItems("Total Adjusted Sales £").Visible = CheckBox37 .PivotItems("Total Commitment").Visible = CheckBox16 .PivotItems("Total Commitment - Cash £").Visible = CheckBox47 .PivotItems("Total Commitment - Nett £").Visible = CheckBox46 .PivotItems("Total Commitment Margin %").Visible = CheckBox60 End With With ActiveSheet.PivotTables(Department).PivotFields("Measure") .PivotItems("Total Stk Margin %").Visible = CheckBox62 .PivotItems("Total Stock Cash £").Visible = CheckBox50 .PivotItems("Total Stock Days Cover").Visible = CheckBox65 .PivotItems("Total Stock Nett £").Visible = CheckBox49 .PivotItems("Total Stock Units").Visible = CheckBox19 End With ' Adds the data fields to the Pivot Table For Each cll In Data.Range(FisCheck, FisCheck.End(xlToRight)).Cells ActiveSheet.PivotTables(Department).AddDataField ActiveSheet.PivotTables( _ Department).PivotFields(FisDte), "Sum " & cll, xlSum Next cll Application.Calculation = xlCalculationAutomatic ' With ActiveSheet.PivotTables("Test").PivotFields("Ave 2016/31") ' .NumberFormat = "0.00%" ' End With ' ActiveSheet.PivotTables("Test").AddDataField ActiveSheet.PivotTables( _ ' "Test").PivotFields("2016/32"), "Ave 2016/32", xlAverage 'With ActiveSheet.PivotTables("Test").PivotFields("Ave 2016/32") ' .NumberFormat = "0.00%" 'End With Unload Me End Sub
Reply With Quote
