Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='769791' date='08-Apr-2009 14:52']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
    [/quote]

    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

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •