f2e4
06-11-2008, 12:18 PM
Dim StaffMember As String
Dim StartDate As Date
Dim temp as Long
'Weekending Date
StartDate = Date + 6 - Weekday(Date)
temp = ListBox1.ListIndex
With ListBox1
StaffMember = .List(temp, 1) & " " & .List(temp, 0)
End With
'Update PivotTable fields
Sheets("Drop Downs").PivotTables("PivotTable1").PivotFields("Staff Member").CurrentPage = StaffMember
Sheets("Drop Downs").PivotTables("PivotTable1").AddDataField Sheets("Drop Downs").PivotTables("PivotTable1"). _
PivotFields(StartDate), "Sum of" & StartDate, xlSum
Sheets("Drop Downs").PivotTables("PivotTable1").DisplayNullString = False
ListBox1.Activate
The staffmember section works fine
However, the datafield always remains the same. How do I get this to actually change to the value I want it to?
Dim StartDate As Date
Dim temp as Long
'Weekending Date
StartDate = Date + 6 - Weekday(Date)
temp = ListBox1.ListIndex
With ListBox1
StaffMember = .List(temp, 1) & " " & .List(temp, 0)
End With
'Update PivotTable fields
Sheets("Drop Downs").PivotTables("PivotTable1").PivotFields("Staff Member").CurrentPage = StaffMember
Sheets("Drop Downs").PivotTables("PivotTable1").AddDataField Sheets("Drop Downs").PivotTables("PivotTable1"). _
PivotFields(StartDate), "Sum of" & StartDate, xlSum
Sheets("Drop Downs").PivotTables("PivotTable1").DisplayNullString = False
ListBox1.Activate
The staffmember section works fine
However, the datafield always remains the same. How do I get this to actually change to the value I want it to?