Poundland
12-09-2015, 02:03 AM
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).Cells
' 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
The full code is below;
[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
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).Cells
' 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
The full code is below;
[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