Excel - Pivot Table Help
Asked By Rich
03-Feb-10 01:03 AM
Help!
I have written some code which manipulates Excel 2007 pivot tables. The
program works fine on my machine and on the guy that sits next to me.
But...3 other people in the organisation have issues with the spreadsheet
crashing.
The code is around this part:
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Activity Date")
.PivotItems("<" & Start_Date).Visible = False
.PivotItems(">" & End_Date).Visible = False
End With
Where 'Start_Date' and 'End_Date' are variables based on data entry.
As I said...works fine on my machine and the second machine I tested it on
but not on the others. Everyone is working on Excel 2007.
What I am trying to do is limit the data input to only show values between
start and end date...
Any clues or fixes out there?
PivotTables
(1)
PivotFields
(1)
PivotItems
(1)
ActiveSheet.PivotTables
(1)
Excel 2007
(1)
Database
(1)
PivotTable2
(1)
RichWhat
(1)
Roger Govier replied to Rich
Hi Rich
What error message is it giving when it crashes?
--
Regards
Roger Govier
__________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________
The message was checked by ESET Smart Security.
http://www.eset.com
button has this code now to control the Page feild of the PT: With ActiveSheet.PivotTables("PivotTable2").PivotFields("Month") .PivotItems("9 / 30 / 2006").Visible = False .PivotItems("10 / 31 / 2006").Visible = False .PivotItems("11 / 30 / 2006").Visible = False .PivotItems("12 / 31 / 2006").Visible = False End With Thus leaving only the 2007 months visable, thus all 12 months in 2007 as well. I was thinking maybe I could set all pivotitems in pivotfeild Month to false if < cell A1 which I could set a formula in What would this code look like, or is there a better way? Excel Programming Discussions PivotTables (1) PivotFields (1) PivotItems (1) ActiveSheet.PivotTables (1) Page (1) PivotFieldDim (1) PivotItemDim (1) ManualUpdate
have the following code: e = sheets("sheet1").range("g3").value msgbox e with sheets("sheet1").pivottables("pivottable3").pivotfields("FAC") .pivotitems(e).visible = true end with but i am getting a runtime error 1004: unable to get the pivotitems property ofthe pivotfield class. i made sure that the value to be made visible is advance, geebee Excel Programming Discussions Macro (1) JRForm (1) ActiveCell (1) MyValue (1) Xlnone (1) Pivotfields (1) Pivottables (1) Pivottable3 (1) geebee, Do you have quotation marks around the value in g3? hi hi, its something wrong somewhere. i even changed the code to the following: with activesheet.pivottables("pivottable3").pivotfields("fac") .pivotitems("SSS").visible = true but i am still getting the same error message. i
a cell range within the sheet? This is what I have Sub Macro12() With ActiveSheet.PivotTables("WON").PivotFields("Month Won") .PivotItems("1").Visible = False .PivotItems("2").Visible = False .PivotItems("3").Visible = False .PivotItems("4").Visible = False .PivotItems("5").Visible = False .PivotItems("6").Visible = False .PivotItems("7").Visible = False .PivotItems("8").Visible = False .PivotItems("9").Visible = False
it against a different file and the Pivot Items are different. 1st day With ActiveSheet.PivotTables("PivotTable5").PivotFields("Problem") .PivotItems("Upgrade").Visible = False .PivotItems("Slow Speed").Visible = False .PivotItems("Cannot Create Customer Account").Visible = False 2nd day With ActiveSheet.PivotTables("PivotTable5").PivotFields("Problem") .PivotItems("Out of Service").Visible = False .PivotItems("Slow Speed").Visible = False .PivotItems("Account Disabled").Visible = False
DefaultVersion: = xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination: = ActiveSheet.Cells (3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables(pivtab).AddFields RowFields: = "date" ActiveSheet.PivotTables(pivtab).PivotFields("0-5 mo").Orientation = _ xlDataField ActiveCell.Offset(8, 0).Range("A1").Select Selection.Group True, End: = True, Periods: = Array(False, False, False, _ False, False, False, True) With ActiveSheet.PivotTables(pivtab).PivotFields("date") .PivotItems("<01 / 09 / 1980").Visible = False .PivotItems("1980").Visible = False .PivotItems("1981").Visible = False .PivotItems("1982").Visible = False .PivotItems("1983").Visible = False .PivotItems("1984").Visible = False