Replies

This one is awesome as well! I’ll try this one out too!
Thanks so much!!
Lana

You make it look so easy! This is awesome!
Thanks Hans!
Lana

We have used Access in the past, however we use msquery to download it into Excel, then we use macros to manipulate the data. Likewise, we have formulas in the database as well. In addition, we don’t have Access on any of our desktops anymore. Do you have any ideas on why the macro to change the range is not working? When we manually “fix” the range in the pivot table everything works fine. Below is the recorded macro for when the database (in 2007) is open and I manually fix the range. It’s wierd that it says C1:C16 as the real range is A1:P1048546???

Sub Macro8()
ActiveSheet.PivotTables(“PivotTable1”).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
“K:GroupsPayroll2008 GL PayrollPayroll UploadsTesting[Trial Balances-NEW.xlsm]data!C1:C16” _
, Version:=xlPivotTableVersion10)
End Sub

Thanks!
Lana

Ahh.. yes, I tried everything except that! Thanks Hans!
Lana

This worked perfect… thanks so much Hans. This code will come in handy for not just this project, but I can think of several others that I can use this for as well.
Thanks again!!!
Lana

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

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

I love it… thanks Hans!!
Lana

The data is coming from our AS400. The software we use is called MAPICS.

I already tried to just type it into the SQL area (see below), but it didn’t work… thought I’d give it a whirl. I also tried using the word AND instead of the & sign, and that didn’t work either. The field called BSNBCD is the Period, so I’m trying to join the BSNBCD field with the letter B (as seen in the SQL below… which didn’t work).

SELECT left(BSJ6CD,2), YABSREP.BSJ6CD, YABSREP.BSBPCD, YABSREP.BSJ7CD, YABSREP.BSBQCD, YABSREP.BSBEVA, YABSREP.BSNBCD, YABSREP.BSGRCD, YABSREP.BSJ9CD, YABSREP.BSGPCD, YABSREP.BSGQCD, YABSREP.BSOMCD, LEFT(BSJCNB,4), YABSREP.BSBEVA, BSNBCD & ‘B’
FROM SCAR.AMFLIB.YABSREP YABSREP
WHERE (YABSREP.BSJCNB Between 200700 And 200912)

Thanks Hans… I’m still having trouble with the formula’s. It always gives me these messages:

Error for when I try using the > sign is this one….
SQL0104 – Token > was not valid. Valid tokens: ),.

Error for when I try using the & sign is this one…
SQL0104 – Token & was not valid. Valid tokens: +- AS .

Now if I just enter ‘B’ in the field, then the letter B shows up down the whole column, but I’m trying to JOIN the period and the letter B. The If statement you gave me gives the 1st error message above.

Any other ideas??

Thanks!
Lana

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

Works perfect!
Thanks Hans!
Lana

I like it…thanks!
Lana

Never mind… I just figured it out… for those interested, here is my formula.
=LEFT(L751,(LEN(L751)-1))

Thanks!!
Lana