Showing pivot table dates

One way is to curser in the date field of the pivot table, right click, select group and ouline,
and then group, and write in the "staring at" and "ending at" dates.

Bruce,
the database is too large and when i tried that it told me there were too many cells to group and then proceeded to lockup my Excel. Is there another way?

Brad

Sorry Brad,
Try this: Select Data, Filter, Auto filter.
Then hit the drop down arrow in the column, select custom, and enter the criteria.

Bruce, you can't filter in a Pivot Table and I don't have access the database, just the pivot table. the database is a separate .csv file.

Brad,
Maybe someone will have a better idea, but the only thing I know to do is to select the pivot table, copy, edit, paste special, values, into a new sheet, and then use the filter,

Hi,

Maybe this will work. Assumes your date is in column a1. Assumes your data does not begin until cell a3.

Cheers,

thomas

Sub Macro1()
Application.Goto Reference:="R1C1"
Application.Goto Reference:="R1C27"
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(NOW()-RC[-26]<35,RC[-26],"""")"
Selection.Copy
ActiveCell.Range("A1:A10000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
Application.Goto Reference:="R1C28"
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",""hide"")"
Selection.Copy
ActiveCell.Range("A1:A10000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
Application.Goto Reference:="R1C28"
ActiveCell.Columns("A:A").EntireColumn.Select
Calculate
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
:=".", FieldInfo:=Array(1, 1)
Application.Goto Reference:="R1C28"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Find(What:="hide", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=True).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireRow.Hidden = True
Application.Goto Reference:="R1C28"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireRow.Hidden = True
Application.Goto Reference:="R1C28"
End Sub
Sub Macro2()
Application.Goto Reference:="R1C28"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Find(What:="hide", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=True).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireRow.Hidden = True
Application.Goto Reference:="R1C28"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireRow.Hidden = True
Application.Goto Reference:="R1C28"

Application.Run "Macro2"
Application.Run "Macro2"
Application.Run "Macro2"
Application.Run "Macro2"
Application.Run "Macro2"
End Sub

oops, i meant column A.

and... you have to run macro1 first, then run macro2 after macro1 finishes running.

cheers,

thomas

You can use a filter within the pivot table by highlighting the first blank cell to the right of your pivot table then dragging left over your pivot table. Then hit data, autofilter, and you will get your filter boxes. This will allow you to filter your pivot table. TS

Tony - I didn't know Excel could do that. Thanks for the great tip!