Macro for change of calculated item formulas

Topic

New Reply

Hello!

I’m needing a bit of VBA code help if possible… I’ve attached an Excel file with an example of what I’m trying to accomplish.

My goal is to have a macro change the pivot table calclated item formulas to reflect what is listed in columns “J” and “L” on the “Periods” worksheet . My problem is that the list may have all 13 periods, or maybe just 3 periods, depending on which period they select (try selecting a different period in cell “I16” and the lists change). I’m not sure how to write that variablity into the macro.

I plan to have multiple pivot tables in one workbook, and when a user changes the period in cell “I16” to select which month he would like to view, the YTD calculated items in the pivot tables will change to what is in columns “J” and “L” on the “Periods” worksheet.

I simply recorded a macro and then tried to input which sheet & cell to look at (see the macros in the example file), but it didn’t work. I’m assuming an IF Then type code would be the route to go, If Sheet7.Range(“E10”) = 12 Then…. have the calculated item add the all 13 items, If Sheet7.Range(“E10”) = 11 Then… have the calculated item add only the 12 periods, etc., but I’m clearly missing something in the simple macro first, so going on to write in the If Then code won’t work until I get that fixed????

As usual, any help is always appreciated!!

Thanks!
Lana

Reply | Quote

Replies

Your macro appears to have been recorded for a different pivot table – there is no pivot field named Period and no calculated item named YTD-Curr Yr.

Also, cell F2 contains a mysterious entry OPN that doesn’t occur anywhere in the data so it’ll cause errors.

Without that item, you could use this:

Code:

Sub ChangePTPeriodsNEW()
  Dim strFormula1 As String
  Dim strFormula2 As String
  Dim r As Long
  r = 2
  Do While Not Worksheets("Periods").Cells(r, 10) = ""
	strFormula1 = strFormula1 & "+" & Worksheets("Periods").Cells(r, 10)
	strFormula2 = strFormula2 & "+" & Worksheets("Periods").Cells(r, 12)
	r = r + 1
  Loop
  If Not strFormula1 = "" Then
	strFormula1 = "=" & Mid(strFormula1, 2)
	Worksheets("Pivot Table").PivotTables("PivotTable1").PivotFields("Month") _
	  .CalculatedItems("YTD-Current Year").StandardFormula = strFormula1
	strFormula2 = "=" & Mid(strFormula2, 2)
	Worksheets("Pivot Table").PivotTables("PivotTable1").PivotFields("Month") _
	  .CalculatedItems("YTD-Prior Year").StandardFormula = strFormula2
  End If
End Sub

Reply | Quote

Your macro appears to have been recorded for a different pivot table – there is no pivot field named Period and no calculated item named YTD-Curr Yr.

Also, cell F2 contains a mysterious entry OPN that doesn’t occur anywhere in the data so it’ll cause errors.

Without that item, you could use this:

Code:

Sub ChangePTPeriodsNEW()
  Dim strFormula1 As String
  Dim strFormula2 As String
  Dim r As Long
  r = 2
  Do While Not Worksheets("Periods").Cells(r, 10) = ""
	strFormula1 = strFormula1 & "+" & Worksheets("Periods").Cells(r, 10)
	strFormula2 = strFormula2 & "+" & Worksheets("Periods").Cells(r, 12)
	r = r + 1
  Loop
  If Not strFormula1 = "" Then
	strFormula1 = "=" & Mid(strFormula1, 2)
	Worksheets("Pivot Table").PivotTables("PivotTable1").PivotFields("Month") _
	  .CalculatedItems("YTD-Current Year").StandardFormula = strFormula1
	strFormula2 = "=" & Mid(strFormula2, 2)
	Worksheets("Pivot Table").PivotTables("PivotTable1").PivotFields("Month") _
	  .CalculatedItems("YTD-Prior Year").StandardFormula = strFormula2
  End If
End Sub

You are right Hans… I had recorded the macro for my actual data, and not my example data, sorry about confusing everyone! Anyway, I will try out your code… I’m sure it’s WAY BETTER then what I came up with (see below… I finally got it to work). Mine will be quite lengthy once I add every IF THEN possibility, and then add in the looping. I like yours better, but I need to study it so I understand it first. You are a master at this, and I’m still somewhat of a beginner, at least compared to you. Thanks for the code/help Hans!!
Lana

Sub ChangePTPeriods()

If Sheet7.Range(“E2”) = 12 Then
Sheet4.Select
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Period”).CalculatedItems( _
“YTD-Curr Yr”).StandardFormula = “=” & Sheet7.Range(“J2″) & ” +” & Sheet7.Range(“J3″) & ” +” _
& Sheet7.Range(“J4″) & ” +” & Sheet7.Range(“J5″) & ” +” & Sheet7.Range(“J6″) & ” +” _
& Sheet7.Range(“J7″) & ” +” & Sheet7.Range(“J8″) & ” +” & Sheet7.Range(“J9″) & ” +” _
& Sheet7.Range(“J10″) & ” +” & Sheet7.Range(“J11″) & ” +” & Sheet7.Range(“J12″) & ” +” _
& Sheet7.Range(“J13”)

ElseIf Sheet7.Range(“E2”) = 11 Then
Sheet4.Select
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Period”).CalculatedItems( _
“YTD-Curr Yr”).StandardFormula = “=” & Sheet7.Range(“J2″) & ” +” & Sheet7.Range(“J3″) & ” +” _
& Sheet7.Range(“J4″) & ” +” & Sheet7.Range(“J5″) & ” +” & Sheet7.Range(“J6″) & ” +” _
& Sheet7.Range(“J7″) & ” +” & Sheet7.Range(“J8″) & ” +” & Sheet7.Range(“J9″) & ” +” _
& Sheet7.Range(“J10″) & ” +” & Sheet7.Range(“J11″) & ” +” & Sheet7.Range(“J12”)

ElseIf Sheet7.Range(“E2”) = 10 Then
Sheet4.Select
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Period”).CalculatedItems( _
“YTD-Curr Yr”).StandardFormula = “=” & Sheet7.Range(“J2″) & ” +” & Sheet7.Range(“J3″) & ” +” _
& Sheet7.Range(“J4″) & ” +” & Sheet7.Range(“J5″) & ” +” & Sheet7.Range(“J6″) & ” +” _
& Sheet7.Range(“J7″) & ” +” & Sheet7.Range(“J8″) & ” +” & Sheet7.Range(“J9″) & ” +” _
& Sheet7.Range(“J10″) & ” +” & Sheet7.Range(“J11”)

Else

Sheet4.Range(“A1”).Select

End If

Reply | Quote

Now that Hans was so kind to provide code for the pivot table calc item change, I have the following code (recorded of course), that changes the current period to the new periods. How would I include in Hans original code, a way to have the new periods (FEB09, FEB08, FEB09B) in the pivot table. As you can see below, I manual unselected the OLD periods of NOV08, NOV07, NOV08B and then selected the new months (FEB). The periods I want to select are in columns 9 of the Periods worksheet.
Thanks so much!!
Lana

Sub test()

With ActiveSheet.PivotTables(“PivotTable2”).PivotFields(“Period2”)
.PivotItems.Visible = True
.PivotItems(“FEB09”).Visible = False
.PivotItems(“FEB09B”).Visible = False
.PivotItems(“FEB08”).Visible = False
.PivotItems(“NOV08”).Visible = True
.PivotItems(“NOV08B”).Visible = True
.PivotItems(“YTD Current Year”).Visible = False
.PivotItems(“YTD Budget”).Visible = False
.PivotItems(“YTD Prior Year”).Visible = False
End With

End Sub

Reply | Quote

Your description doesn’t match the workbook that you attached (column I doesn’t contain a list of months).

You might be better of recreating the pivot table from scratch.

Reply | Quote

Per Hans suggested… I revised the attachment so it made more sense. Below is what I’m trying to accomplish.

Hans has given me code for the orignal part of this post to work… now I need to get the months in column “K” to change in the pivot table as well. (My current macro is just hard coded for now). For example, the pivot table has OCT08, OCT07, OCT08B, YTD Current Year, YTD Prior Year, and YTD Budget as “visible”. When the month in cell “P2” changes, then I need the new months in column “K” to be visible in the pivot table. Of course the calculated items (YTD Current Year, YTD Last Year, & YTD Budget) will already be visible, but I need to make the old months NOT visible, and the new months visible. So if we changed cell “P2” to FEB09, the months in column “K” change to FEB09, FEB09B & FEB08, so I’d need the OCT08, OCT08B & OCT07 to NOT be visible and the FEB09, FEB09B, & FEB08 to be VISIBLE.

Thanks!!
Lana

Reply | Quote

Try this version:

Code:

Sub CurrPeriods()
  Dim pit As PivotItem
  Dim rng As Range
  
  For Each pit In Sheet2.PivotTables("PivotTable1").PivotFields("Month").PivotItems
	If Not (pit.Name = "YTD Current Year" Or pit.Name = "YTD Prior Year" Or _
		pit.Name = "YTD Budget") Then
	  Set rng = Sheet8.Range("K2:K14").Find(What:=pit.Name, LookIn:=xlValues, LookAt:=xlWhole)
	  pit.Visible = Not (rng Is Nothing)
	End If
  Next pit
End Sub

Reply | Quote