Selecting Pivot Items between set dates

**joelmurphy** · 06-21-2010, 05:11 AM

Hi,

I have a pivot table with data imported from another spreadsheet source on a weekly basis. The pivot displays Sales Rep name, created (a date & time value) down the left hand side and revenue summed in the table area.
Each monday i want to update the pivot table with new data and only display the rows with dates for the previous week. e.g. for this week show dates between 14/06/2010 and 20/06/2010. The report will normally be created on the monday of the following week so maybe something using today () -7 extra.

I had a go and go pretty close using cell values as the days I wanted to display but have lost the code.

Any Ideas?

Thanks

**joelmurphy** · 06-21-2010, 12:23 PM

I have been able to change the dates to just date with no time. is there a way of getting all pivot items between two dates?

**joelmurphy** · 06-22-2010, 04:45 AM

i tried the code below and it worked when i manually entered a date where i have written "date()" but using date() does not work. Have I got the code slightley wrong? Or can I use a input box to get the user to enter a date range, or perhaps just look up a value in the spreadsheet?

Sub Macro7()


    

 With ActiveSheet.PivotTables("PivotTable1").PivotFields("Created")
        For i = 1 To .PivotItems.Count
            If .PivotItems(i).Name < "date()" Then
                .PivotItems(i).Visible = False
            End If
        Next i
    End With




End Sub

**romperstomper** · 06-22-2010, 04:57 AM

Your code is currently testing against the literal string "date()" - you need to remove the quotes. I'd also explicitly convert the item name to a date using CDate:

Sub Macro7()
 With ActiveSheet.PivotTables("PivotTable1").PivotFields("Created")
        For i = 1 To .PivotItems.Count
            If CDate(.PivotItems(i).Name) < Date() Then
                .PivotItems(i).Visible = False
            End If
        Next i
    End With

End Sub

**joelmurphy** · 06-22-2010, 05:12 AM

Thanks that works but i get a run time error '13' type missmatch then the second half does not work and has the same error, even if i comment out the first half

Sub Macro7()

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Created")
       For i = 1 To .PivotItems.Count
        If CDate(.PivotItems(i).Name) < Date - 7 Then
              .PivotItems(i).Visible = False
          End If
      Next i
   End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Created")
       For i = 1 To .PivotItems.Count
         If CDate(.PivotItems(i).Name) > Date - 1 Then
         .PivotItems(i).Visible = False
           End If
      Next i
    End With


End Sub

**romperstomper** · 06-22-2010, 05:20 AM

Then I'd guess you have some blanks or non-date values in the field. There's also no point in looping twice through the same data:


Sub Macro7()

    With ActiveSheet.PivotTables("PivotTable1")
        .ManualUpdate = True
        With .PivotFields("Created")
            For i = 1 To .PivotItems.Count
                If IsDate(.PivotItems(i).Name) Then
                    If CDate(.PivotItems(i).Name) < Date - 7 Or CDate(.PivotItems(i).Name) > Date - 1 Then
                        .PivotItems(i).Visible = False
                    Else
                        .PivotItems(i).Visible = True
                    End If
                Else
                    .PivotItems(i).Visible = False
                End If
            Next i
        End With
        .ManualUpdate = False
    End With

End Sub