test if more than one year

No it won't!

SUMPRODUCT is a worksheet function, not a VBA command. Worse still, it cannot even be called from the WorksheetFunction object/property.

You need to evaluate it

[vba]

Dim sFormula As String
sFormula = "SUMPRODUCT(--(YEAR(A2:A" & _
.Cells(.Rows.Count, 1).End(xlUp).Row & _
")=YEAR(A2)))=COUNT(A2:A" & _
.Cells(.Rows.Count, 1).End(xlUp).Row & ")"

If ActiveSheet.Evaluate(sFormula) > 1 Then
[/vba]

This is the code I got out of that:

[vba]Dim pf As PivotField
sFormula = "SUMPRODUCT(--(YEAR(B2:B" & Cells(Rows.Count, 2).End(xlUp).Row & _
")=YEAR(B2)))=COUNT(B2:B" & Cells(Rows.Count, 2).End(xlUp).Row & ")"

Set pf = pt.PivotFields("Date")
If detail.Evaluate(sFormula) = 1 Then
pf.LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False _
, False, True, False, False)
If PivotTableOptions.Descending.Value = True Then
pt.PivotFields("Date").AutoSort xlDescending, "Date"
End If
End If
If detail.Evaluate(sFormula) > 1 Then
pf.LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False _
, False, True, False, True)
If PivotTableOptions.Descending.Value = True Then
pt.PivotFields("Date").AutoSort xlDescending, "Date"
pt.PivotFields("Years").AutoSort xlDescending, "Years"
End If
End If[/vba]

I am trying to group the pivottable data field of date by year if more than one year but don't group it by year if it is only one year. Detail is the name of the sheet with data. Can I get that out of this code?

Just bumping it up

Does anyone know the problem? I have tried many different ways of fixing it. It might be something small that I never thought of or it may be large.

Without testing your code myself, if you step through your code, what does "sFormula" evaluate to?

how do I check what it evaluates to? I can never get the immediate window or anything like that working.

Step through the code and after sFormula is set, type

?sFormula in the immediate window.

Thank you xld

I stepped through the code and it is giving me a type mismatch error on the line that evaluates it. I had to change the formula a little:

[VBA]sFormula = "SUMPRODUCT(--(YEAR(detail.B2:B" & Cells(detail.Rows.Count, 2).End(xlUp).Row & _
")=YEAR(B2)))=COUNT(detail.B2:B" & Cells(detail.Rows.Count, 2).End(xlUp).Row & ")"[/VBA]

Since at the time that it evaluates the formula, I am on another worksheet than the one I needed to evaluate I added the name of the worksheet (I mean I set the worksheet = detail)

Assuming that the worksheet is called detail, you need

[vba]

Dim sFormula
With Worksheets("detail")
sFormula = "SUMPRODUCT(--(YEAR(detail!B2:B" & .Cells(.Rows.Count, 2).End(xlUp).Row & _
")=YEAR(B2)))=COUNT(detail!B2:B" & .Cells(.Rows.Count, 2).End(xlUp).Row & ")"
End With
[/vba]

XLD,

Would that work if I don't know the name of the tab? I set that sheet = detail but the name may be something different.

Another problem is when I step through the code and use the immediate window after the formula is evaluated the sFormula is equal to the formula not the answer. Hence it isn't working?

You can use

Activesheet.Evaluate(sFormula)

to see the result

xld when I use that in the immediate window it comes up blank: the result doesn't pop up and neither does an error

I am sorry, it is difficult to determine from afar.

Type ?sformula in the immediate window and tell us what you see.

This is what I get in the immediate window:

[VBA]SUMPRODUCT(--(YEAR(detail!B2:B7155)=YEAR(B2)))=COUNT(detail!B2:B7155)[/VBA]

I am just bumping this back up

Put that formula in a worksheet, and see what the result is.

ok I will try that