|
How Do I Pull The Current Month For Use In Pivot Table Start Date?
|
|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
How Do I Pull The Current Month For Use In Pivot Table Start Date? - Excel
|
View Answers
|
|
|
I am having trouble starting my pivot table data from the first of the current month. The "Now" function is too specific. I also have tried Month(now) but that doesn't work. Here is what i have:
Code:
PT.PivotFields("DueDate").LabelRange.Group _
Start:=Now, End:=True, Periods:=Array(False, False, False, _
False, True, False, True)
Similar Excel Video Tutorials
Count Months from Start and End Date
- Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm Formula to Count Months from Start and End Date. See the functions DATEDIF, DAT ...
PivotTable: Average w MONTH criteria
- Create an Average with multiple criteria, including month criterion using a Pivot Table (PivotTable). See the grouping Pivot Table feature. ...
Similar Topics
Hello everyone
I have multiple pivot tables on one sheet.
What I am trying to do is:
Group the date of every pivot table by month and by year.
I have the code working for one pivot table
VB:
ActiveWorkbook.Worksheets("Basic").PivotTables("Table1").PivotFields("Date").LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
But it is not working for each pivot table.
VB:
Dim pt As PivotTable
Worksheets("Basic").Select
For Each pt In ActiveSheet.PivotTables
pt.PivotFields("Date").LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
Next pt
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
With the above code I get the error:
Run Time error '1004':
Unable to get the PivotFields property of the PivotTable class.
Anyone got an idea what is going wrong?
Thanks in advance!
Hi there,
I'm trying to use some code to create a pivot table.
When I record a macro I get the following code:
Code:
Range("B3").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False _
, False, True, False, False)
Which works fine although my pivot table will not always be created in the same place in the report.
I therefore thought I might be able to do this:
Code:
ActiveSheet.PivotTables("PivotTable1").ColumnFields("Changed On").Group Start:=True, End:=True, _
Periods:=Array(False, False, False, False, True, False, False)
But I get the message:
Run-Time error '438':
Object doesn't support this property or method
Any idea how to go about grouping by selecting the fields within the pivot table rather than the cell reference itself?
Dom
Hello,
I'm trying to build a macro for Pivot table creation. Everything works fine but somehow I cannot group my date field to months and years. I have tried three different codes and every one of these works when I run the macro in the break mode (uising F8). But when I run the macro from Excel, I get an error message saying 'Runtime error 1004: cannot group that selection.
The first one
Code:
Set myF = pt.PivotFields("Kirjaus pvm + klo")
myF.LabelRange.Group Start:=True, End:=True, periods:=Array(False, False, False, False, True, False, True)
The second
Code:
pt.PivotFields("Kirjaus pvm + klo").DataRange.Cells(1).Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
And the third
Code:
Range("B2").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
My macro looks like this
Code:
Sub Pivot_macro()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Sheet1")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Sheet2")
Dim PTCache As PivotCache
Dim PRange As Range
' Delete previous piivot table
Sheets("Sheet2").Select
Cells.Select
Selection.Delete Shift:=xlUp
' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), TableName:="SamplePivot")
'Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True
' Set up the row fields
pt.AddFields RowFields:=Array("Asiakkaan nimi", "Kirjaus pvm + klo")
' Set up the data fields
With pt.PivotFields("Kirjaus pvm + klo")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With
'Grouping
Set myF = pt.PivotFields("Kirjaus pvm + klo")
myF.LabelRange.Group Start:=True, End:=True, periods:=Array(False, False, False, False, True, False, True)
'pt.PivotFields("Kirjaus pvm + klo").DataRange.Cells(1).Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
'Range("B2").Select
'Selection.Group Start:=True, End:=True, periods:=Array(False, False, False, False, True, False, True)
'Move years to columns
With pt.PivotFields("Years")
.Orientation = xlColumnField
.Position = 1
End With
' Now calc the pivot table
pt.ManualUpdate = False
End Sub
Any help would be most appreciated
Hi,
I'm using a macro to creat a pivot table. But when I try and group the date by month and year it errors because there is a blank column added. This blank column is not added when I create the table manually. Any ideas??
Code:
With ActiveSheet.PivotTables("Top 5 Customers").PivotFields("Invoice Date")
.Orientation = xlColumnField
.Position = 1
End With
Range("B3").Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, True)
I have a pivot table in which "PO or Plan" is one of the row items. Currently, due to Alphabetical order, cells that say "Planned Order" are first, with "PO11123..." following in chronological order. In the GUI, you can highlight the cell, drag is to the bottom of the list, and the rest follow, but I can't figure out how to do it in the code.
Recording a macro shows it being given a static position, which won't work for me since the pivot table source data changes daily. Any ideas? (see attached image for screenshot)
Code:
Sub CreatePivotTable()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("PO")
Dim WSR As Worksheet
' Delete old pivot table worksheet and create a new one
Application.DisplayAlerts = False
Sheets("OpenQty Table").Delete
Application.DisplayAlerts = True
Sheets.Add.Name = "OpenQty Table"
ActiveSheet.Move after:=Worksheets(Worksheets.Count)
Sheets("PO").Select
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=Worksheets("OpenQty Table").Range("A2"), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row and column fields
PT.AddFields RowFields:=Array("Prefered Supplier", "M-Spec", "PO or Plan"), ColumnFields:="DueDate"
' Set up the data field
With PT.PivotFields("OpenQty")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
' Ensure that you get zeroes instead of blanks in the data area
PT.NullString = "0"
' Calc the pivot table to allow the date label to be drawn
PT.ManualUpdate = False
PT.ManualUpdate = True
'Focus on pivot table worksheet to allow DueDate grouping by month
Sheets("OpenQty Table").Select
' Group DueDate by month
Worksheets("OpenQty Table").Range("D2").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub
I was trying to help another board member with their question, but it led me to a question of my own. I am trying to get this array to work:
Code:
{=LARGE(ROW($A$1:$A$234)*NOT(ISERR(INDIRECT("Sheet1!Assets"&(ROW($A$1:$A$234))))),1)}
I named a range Assets234. I used ROW($A$1:$A$234) to return an array of numbers. That was then concatenated with "Sheet1!Assets". The INDIRECT function would then turn these into references. Since some of these aren't legitimate references I received a #REF! error. I then used the ISERR function to determine which of the references in the array evaluated to an error. I needed to multiply ROW($A$1:$A$234) against the results and ISERR had the opposite results I needed so I used NOT() to change the TRUEs to FALSEs and vice versa. Knowing that Excel sees False as 0 and True as any other number I multiplied the two arrays. Since everything was 0 except for the last value the LARGE() function should have returned 234. Instead it returns 0. Does anyone know why I get these results? Here is the formula evaluated one step at a time (I did my best to wrap them to the screen so you wouldn't have to browse to the right):
Code:
{=LARGE(ROW($A$1:$A$234)*NOT(ISERR(INDIRECT("Sheet1!Assets" &(ROW($A$1:$A$234))))),1)}
Code:
=LARGE(ROW($A$1:$A$234)*NOT(ISERR({#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;2})),1)
Code:
=LARGE(ROW($A$1:$A$234)*NOT({TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; FALSE}),1)
Code:
=LARGE(ROW($A$1:$A$234)*{FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},1)
Code:
=LARGE({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21; 22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42; 43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63; 64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84; 85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100;101;102;103; 104;105;106;107;108;109;110;111;112;113;114;115;116;117;118; 119;120;121;122;123;124;125;126;127;128;129;130;131;132;133; 134;135;136;137;138;139;140;141;142;143;144;145;146;147;148; 149;150;151;152;153;154;155;156;157;158;159;160;161;162;163; 164;165;166;167;168;169;170;171;172;173;174;175;176;177;178; 179;180;181;182;183;184;185;186;187;188;189;190;191;192;193; 194;195;196;197;198;199;200;201;202;203;204;205;206;207;208; 209;210;211;212;213;214;215;216;217;218;219;220;221;222;223; 224;225;226;227;228;229;230;231;232;233;234}*{FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;TRUE},1)
Code:
=LARGE({0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;234},1)
Code:
=234
Hello there,
I need help with the following code:
Code:
Dim Mymonth As String
Mymonth = Format(Date, "mmm")
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Month")
.PivotItems("Jan").Visible = False
.PivotItems("Feb").Visible = False
.PivotItems("Mar").Visible = False
.PivotItems("Apr").Visible = False
.PivotItems("May").Visible = False
.PivotItems("Jun").Visible = False
.PivotItems("Jul").Visible = False
.PivotItems("Aug").Visible = False
.PivotItems("Sep").Visible = False
.PivotItems("Oct").Visible = False
.PivotItems("Nov").Visible = False
.PivotItems("Dec").Visible = False
.PivotItems(Mymonth).Visible = True
End With
I need help with adding Just the Current month to the Pivot. What I have tried is not working.
Any ideas?
Thanks!
I would like to take the data from a pivot table and compile it into a preset array or table.
How would I go about doing this?
Table would look like:
RPM/MAP 20 30 40 50 60 to 100
600
700
800
900
to
5200
Cells be complied of the O2 Averages in the pivot table below.
This is the pivot table I am using:
Code:
Sub Average()
'
' Average Macro
'
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"FuelData!R1C1:R1048576C3", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="FuelData!R1C5", TableName:="PivotTable7", _
DefaultVersion:=xlPivotTableVersion12
Sheets("FuelData").Select
Cells(1, 5).Select
With ActiveSheet.PivotTables("PivotTable7").PivotFields(" RPM")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields(" MAP")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields(" O2 Sensor 1")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields(" O2 Sensor 1"), "Count of O2 Sensor 1", xlCount
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Count of O2 Sensor 1" _
)
.Caption = "Average of O2 Sensor 1"
.Function = xlAverage
.NumberFormat = "0.00"
End With
Range("E5").Select
ActiveSheet.PivotTables("PivotTable7").PivotFields(" RPM").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable7").PivotFields(" RPM").Subtotals = Array( _
True, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable7").PivotFields(" RPM"). _
LayoutSubtotalLocation = xlAtBottom
With ActiveSheet.PivotTables("PivotTable7")
.ColumnGrand = False
.RowGrand = False
.ShowDrillIndicators = False
End With
ActiveSheet.PivotTables("PivotTable7").PivotFields(" RPM"). _
LayoutSubtotalLocation = xlAtTop
ActiveSheet.PivotTables("PivotTable7").PivotFields(" RPM").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
End Sub
I am trying to build pivot tables using the examples in the book "Pivot Table Data Crunching for Microsoft Office Excel 2007" chap 11. I am running into all kinds of issues and problems.
First I found out that using
Set PRange = WR1.Cells(1, 1).Resize(FinalRow, FinalCol) doesn't always work if the FinalRow is above a certain value. I don't know what that value is. I had to result to using
PRange = "Report 1!R1C1:R" & FinalRow & "C" & FinalCol & "" to get that part of the code to work for me. But then, this part of the code started generating an error
PT.PivotFields("Milestone Date").LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
Initially I thought the issue was having a blank cell in a date column but now I don't have any blank cell in the column and I still get the "Group method of Range class failed"
Any help will be greatly appreatiated.
Thanks
Ayo
i am using excel 20047, below is mu code , it is working fine till i reach the code of grouping date ( highlighted in red), it give me debug ( error 1004).
appreciate any assistance
'Create Pivot Table
Set wsd = ActiveSheet
'delete any existing pivot tables
For Each pt In wst.PivotTables
pt.TableRange2.Clear
Next pt
wst.Range("I1:Z1").EntireColumn.Delete
'define the imput area of the pivot table, it means finalrow and finalcol
lastrow = wst.Cells(Rows.Count, 1).End(xlUp).Row
finalcol = wst.Cells(1, Columns.Count).End(xlToLeft).Column
'set pivot range
Set prange = wst.Cells(1, 1).Resize(lastrow, finalcol)
'setup pivot cache,it exist in memory and hold all the original data set
Set ptcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=prange.Address)
'create pivot table
Set pt = ptcache.CreatePivotTable(TableDestination:=wst.Cells(2, finalcol + 2), TableName:="Pivotziad")
'format pivot
pt.ShowTableStyleRowStripes = True
pt.TableStyle2 = "PivotStyleDark3"
'tur'n off updating while building the table
pt.ManualUpdate = True
'set up the row fields
pt.AddFields RowFields:=Array("Staff", "Date"), ColumnFields:="Data"
'set up data fields
With pt.PivotFields("Round Up")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
.Name = " Round Up"
End With
pt.NullString = "0"
pt.ManualUpdate = False
pt.ManualUpdate = True
wst.Activate
' group bonus date by month ,quater , year
pt.PivotFields(" Date").LabelRange.Group Start:=True, _
End:=True, Periods:= _
Array(False, False, False, False, True, True, True)
pt.ManualUpdate = False
pt.ManualUpdate = True
Okay, this post may wander a little. I'll try to be as concise as I can.
In the attachment are two sheets. "Expense" WAS a pivot, but would be way too large to post here, so it's only the values. I hope it helps to see the problem. "Lookups" is a sheet I generate to help determine values to use in changing the pivot table.
Background: The pivot is not my creation, I inherit it, so can't impact the source data. The pivot pulls in DATES, oddly formatted, in the MOS field. Each month 12 analysts each create multiple projections, and need the expenses by date from the beginning of our fiscal year to the month of the projection. My function is to automate all of that.
The Problem today: The pivot shows dates AFTER our projection months. Need to remove those via VBA.
In the example pivot, 062011,072011,and 082011 are after our projection month, so I'd like to be able to delete them. In past versions, the dates were formatted as 06, 07, and 08, and I was able to remove them via macro, using the data in "Lookups". However, I don't know how to populate column A in "Lookups" with the date formats as in this current pivot.
Here's my macro for populating column a in "Lookups". I need to know how to change that to the format "MMYYYY" during the creation.
Code:
'add a column for the full range of months for the year
Columns("A:A").NumberFormat = "@"
[A1:A12] = [index(text(row(1:12),"'00"),)]
Here's the code I was using to limit my months ("TwoB" is the name of a temporary workbook I use in the process)
Code:
With pt.PivotFields("MOS2")
.PivotItems("01").Visible = False
.PivotItems("02").Visible = False
.PivotItems("03").Visible = False
.PivotItems("04").Visible = False
.PivotItems("05").Visible = False
.PivotItems("06").Visible = False
.PivotItems("07").Visible = False
.PivotItems("08").Visible = False
.PivotItems("09").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems(TwoB.Sheets("Lookups").Range("$D3").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D4").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D5").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D6").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D7").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D8").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D9").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D10").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D11").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D12").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D13").text).Visible = True
.PivotItems(TwoB.Sheets("Lookups").Range("$D14").text).Visible = True
.PivotItems("(blank)").Visible = False
.PivotItems("BUDGET").Visible = False
.PivotItems("Enc/Pre-Enc").Visible = False
.PivotItems("PROJECTED").Visible = False
End With
any help on this is greatly appreciated.
Thank you,
John
I have a pivot table in which "PO or Plan" is one of the row items. Currently, due to Alphabetical order, cells that say "Planned Order" are first, with "PO11123..." following in chronological order. I want the "Planned Order" items at the bottom of the list. In the excel GUI, I can highlight the cell, drag is to the bottom of the list, and the rest follow, but I can't figure out how to do it in the code.
Recording a macro shows it being given a static position, which won't work for me since the pivot table source data changes daily. I also can't do reverse alphabetical because then the PO numbers are backwards. Any ideas?
Current:
Planned Order
PO116560
PO116810
PO116854
PO117061
PO117255
PO117427
PO117657
What I want:
PO116560
PO116810
PO116854
PO117061
PO117255
PO117427
PO117657
Planned Order
Code:
Sub CreatePivotTable()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("PO")
Dim WSR As Worksheet
' Delete old pivot table worksheet and create a new one
Application.DisplayAlerts = False
Sheets("OpenQty Table").Delete
Application.DisplayAlerts = True
Sheets.Add.Name = "OpenQty Table"
ActiveSheet.move after:=Worksheets(Worksheets.Count)
Sheets("PO").Select
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=Worksheets("OpenQty Table").Range("A1"), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row and column fields
PT.AddFields RowFields:=Array("Prefered Supplier", "M-Spec", "PO or Plan"), ColumnFields:="DueDate"
' Set up the data field
With PT.PivotFields("OpenQty")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
' Ensure that you get zeroes instead of blanks in the data area
PT.NullString = "0"
' Calc the pivot table to allow the date label to be drawn
PT.ManualUpdate = False
PT.ManualUpdate = True
'Focus on pivot table worksheet to allow DueDate grouping by month
Sheets("OpenQty Table").Select
' Group DueDate by month, year
PT.PivotFields("DueDate").LabelRange.Group _
Start:=Now, End:=True, Periods:=Array(False, False, False, _
False, True, False, True)
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub
Is there a better way to group dates by months in Pivot Tables with VBA?
So far the only way to group dates in Pivot Table with VBA is the following:
Code:
Range("A4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
Frankly, I hate having to select a range before I can group the selection. Selecting a "random" cell doesn't make sense to me and I believe there has to be a better way to do the same. I just can't figure out how. Can somebody help me?
I wrote some VBA code in 2007 that stopped working when I recently upgraded to office 2010. It runs on a pivot table with names in the rows, dates in the columns and a count in the value fields. The code is:
Code:
Dim StartDate As Date
Dim StatusHeader As String, IndividualHeader As String
StatusHeader = "Prep Complete"
IndividualHeader = Preparer
' Hide blank dates
TheTable.PivotFields(StatusHeader).PivotItems("00-01-1900").Visible = False
TheTable.PivotFields(IndividualHeader).PivotItems("").Visible = False
' Calc the pivot table to allow the date label to be drawn
TheTable.ManualUpdate = False
TheTable.ManualUpdate = True
' Figure out the the minimum date
StartDate = TheTable.PivotFields(StatusHeader).LabelRange.Offset(1, 0).Value
' Figure out the first Monday before the minimum date
StartDate = StartDate - Weekday(StartDate, 3)
' Group Date by Week.
TheTable.PivotFields(StatusHeader).LabelRange.Group Start:=StartDate, End:=True, By:=7, Periods:=Array(False, False, False, True, False, False, False)
' Blanks are back and will be grouped in a column < StartDate, so hide this column
TheTable.PivotFields(StatusHeader).PivotItems("
Below is my code to create a Pivot Table. Occasionally, I get an out of memory error message. Do I need to clear something like the pivotcache to prevent this problem?
Code:
Sub createPivotTable()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("SKU Pricing")
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).row
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)
Sheets.Add
ActiveSheet.Name = "SKU Pivot"
Set PT = PTCache.createPivotTable(TableDestination:=Worksheets("SKU Pivot").Cells(2, 2), _
TableName:="Pivot1")
PT.ManualUpdate = True
' Set up the row & column fields
PT.AddFields RowFields:=Array("Order", "Option Name & Description", "SKU", "QTY"), _
ColumnFields:=Array("Style Order", "Style Name")
' Set up the data fields
With PT.PivotFields("SKU Net Price")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
PT.PivotFields("SKU Net Price").Subtotals = Array(False, False, False, False, _
False, False, False, False, False, False, False, False)
PT.PivotFields("SKU").Subtotals = Array(False, False, False, False, _
False, False, False, False, False, False, False, False)
PT.PivotFields("Order").Subtotals = Array(False, False, False, False, _
False, False, False, False, False, False, False, False)
PT.PivotFields("Style Order").Subtotals = Array(False, False, False, False, _
False, False, False, False, False, False, False, False)
PT.ColumnGrand = False
PT.RowGrand = False
PT.ManualUpdate = False
PT.ManualUpdate = True
Columns("B:B").EntireColumn.Hidden = True
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Rows("3:3").EntireRow.Hidden = True
Rows("4:4").WrapText = True
Rows("4:4").RowHeight = 90
Columns("F:Z").ColumnWidth = 20
End Sub
Hello sages,
I use a VBA script to set certain pivot table items visible and not visible like so:
Code:
Sheets("WASH Data").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Responsibility")
.PivotItems("108, BP SHOES").Visible = False
.PivotItems("110, WOMENS SHOES").Visible = False
.PivotItems("112, MENS SHOES").Visible = False
.PivotItems("114, KIDS SHOES").Visible = False
.PivotItems("210, BP/RACK").Visible = False
.PivotItems("211, TBD/SAV/COL/ACT").Visible = False
.PivotItems("212, POV/PETITES").Visible = False
.PivotItems("213, ENCORE").Visible = False
.PivotItems("214, INDY/COATS/DRESS").Visible = False
.PivotItems("216, NARRATIVE/STUDIO").Visible = False
.PivotItems("310, MENSWEAR").Visible = False
.PivotItems("320, KIDSWEAR").Visible = False
.PivotItems("510, ACCESSORIES-SOFTLINES").Visible = True
.PivotItems("512, ACCESSORIES-HARDLINES").Visible = True
.PivotItems("514, WMN SPEC & AT HOME").Visible = True
.PivotItems("999, UNASSIGNED").Visible = False
End With
However, from month to month, these designations change: the numeric department number remains the same, but the department name associated with it may be different.
Is there a way to use a wildcard in this situation? For example, could the .PivotItems expression be structured as such or in some way similar:
Code:
.PivotItems("108*").Visible = False
This would eliminate the need to edit each line in the subroutine every time we make a departmental change internally.
Thanks and best regards,
ACurtis802
Sub GroupPivotDates(InPivTable As Excel.PivotTable, strMonthOrQuarter As String)
Dim xlPivDateField As Excel.PivotField, xlGroupRange As Excel.Range
Set xlPivDateField = InPivTable.PivotFields("Dates")
Set xlGroupRange = xlPivDateField.DataRange
If strMonthOrQuarter = "Month" Then
xlGroupRange.Cells(1).Group Periods:= _
Array(False, False, False, False, True, False, True)
Else
xlGroupRange.Cells(1).Group Periods:= _
Array(False, False, False, False, False, True, True)
End If
Set xlPivDateField = Nothing
Set xlGroupRange = Nothing
End Sub
I've created a macro to create a Pivot Table.
What im extracting is a list of errors that were created since the system started.
When i do the pivot manually i want to know how many were created yesterday.
I want to know how i can amend the macro code for it automatically select "the last business day"
We also need to keep in mind that if for any reason there werent any errors created today, i dont want to the go back to "the last business day" then because then it will go back one extra day. I think this part of my issue can be done with an if statement?
this is my code so far:
Code:
Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!C1:C19").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable5", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable5").AddFields RowFields:="Created Date"
ActiveSheet.PivotTables("PivotTable5").PivotFields("Parter Number"). _
Orientation = xlDataField
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Created Date")
.PivotItems("23/02/2008").Visible = False
.PivotItems("25/02/2008").Visible = False
.PivotItems("26/02/2008").Visible = False
.PivotItems("27/02/2008").Visible = False
.PivotItems("28/02/2008").Visible = False
.PivotItems("29/02/2008").Visible = False
.PivotItems("1/03/2008").Visible = False
.PivotItems("3/03/2008").Visible = False
.PivotItems("4/03/2008").Visible = False
.PivotItems("5/03/2008").Visible = False
.PivotItems("6/03/2008").Visible = False
.PivotItems("7/03/2008").Visible = False
.PivotItems("8/03/2008").Visible = False
.PivotItems("9/03/2008").Visible = False
.PivotItems("11/03/2008").Visible = False
.PivotItems("12/03/2008").Visible = False
.PivotItems("13/03/2008").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Created Date")
.PivotItems("14/03/2008").Visible = False
.PivotItems("15/03/2008").Visible = False
.PivotItems("17/03/2008").Visible = False
.PivotItems("18/03/2008").Visible = False
.PivotItems("19/03/2008").Visible = False
.PivotItems("20/03/2008").Visible = False
.PivotItems("25/03/2008").Visible = False
.PivotItems("26/03/2008").Visible = False
.PivotItems("27/03/2008").Visible = False
.PivotItems("28/03/2008").Visible = False
.PivotItems("29/03/2008").Visible = False
.PivotItems("31/03/2008").Visible = False
.PivotItems("1/04/2008").Visible = False
.PivotItems("2/04/2008").Visible = False
.PivotItems("3/04/2008").Visible = False
.PivotItems("4/04/2008").Visible = False
.PivotItems("5/04/2008").Visible = False
.PivotItems("7/04/2008").Visible = False
.PivotItems("8/04/2008").Visible = False
.PivotItems("9/04/2008").Visible = False
.PivotItems("10/04/2008").Visible = False
.PivotItems("11/04/2008").Visible = False
.PivotItems("12/04/2008").Visible = False
.PivotItems("14/04/2008").Visible = False
.PivotItems("15/04/2008").Visible = False
.PivotItems("16/04/2008").Visible = False
.PivotItems("17/04/2008").Visible = False
.PivotItems("18/04/2008").Visible = False
.PivotItems("19/04/2008").Visible = False
.PivotItems("21/04/2008").Visible = False
.PivotItems("22/04/2008").Visible = False
.PivotItems("23/04/2008").Visible = False
.PivotItems("24/04/2008").Visible = False
.PivotItems("26/04/2008").Visible = False
.PivotItems("28/04/2008").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Created Date")
.PivotItems("29/04/2008").Visible = False
.PivotItems("30/04/2008").Visible = False
.PivotItems("1/05/2008").Visible = False
.PivotItems("2/05/2008").Visible = False
.PivotItems("3/05/2008").Visible = False
.PivotItems("4/05/2008").Visible = False
.PivotItems("5/05/2008").Visible = False
.PivotItems("6/05/2008").Visible = False
.PivotItems("7/05/2008").Visible = False
.PivotItems("8/05/2008").Visible = False
.PivotItems("9/05/2008").Visible = False
.PivotItems("10/05/2008").Visible = False
.PivotItems("12/05/2008").Visible = False
.PivotItems("13/05/2008").Visible = False
.PivotItems("14/05/2008").Visible = False
.PivotItems("15/05/2008").Visible = False
.PivotItems("16/05/2008").Visible = False
.PivotItems("17/05/2008").Visible = False
.PivotItems("19/05/2008").Visible = False
.PivotItems("20/05/2008").Visible = False
.PivotItems("21/05/2008").Visible = False
.PivotItems("22/05/2008").Visible = False
.PivotItems("23/05/2008").Visible = False
.PivotItems("24/05/2008").Visible = False
.PivotItems("26/05/2008").Visible = False
.PivotItems("27/05/2008").Visible = False
.PivotItems("28/05/2008").Visible = False
.PivotItems("29/05/2008").Visible = False
.PivotItems("30/05/2008").Visible = False
.PivotItems("31/05/2008").Visible = False
.PivotItems("2/06/2008").Visible = False
.PivotItems("3/06/2008").Visible = False
.PivotItems("4/06/2008").Visible = False
.PivotItems("5/06/2008").Visible = False
.PivotItems("6/06/2008").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Created Date")
.PivotItems("7/06/2008").Visible = False
.PivotItems("8/06/2008").Visible = False
.PivotItems("9/06/2008").Visible = False
.PivotItems("10/06/2008").Visible = False
.PivotItems("11/06/2008").Visible = False
.PivotItems("12/06/2008").Visible = False
.PivotItems("13/06/2008").Visible = False
.PivotItems("14/06/2008").Visible = False
.PivotItems("16/06/2008").Visible = False
.PivotItems("17/06/2008").Visible = False
.PivotItems("18/06/2008").Visible = False
.PivotItems("19/06/2008").Visible = False
.PivotItems("20/06/2008").Visible = False
.PivotItems("21/06/2008").Visible = False
.PivotItems("22/06/2008").Visible = False
.PivotItems("23/06/2008").Visible = False
.PivotItems("24/06/2008").Visible = False
.PivotItems("25/06/2008").Visible = False
.PivotItems("26/06/2008").Visible = False
.PivotItems("27/06/2008").Visible = False
.PivotItems("28/06/2008").Visible = False
.PivotItems("30/06/2008").Visible = False
.PivotItems("1/07/2008").Visible = False
.PivotItems("2/07/2008").Visible = False
.PivotItems("3/07/2008").Visible = False
.PivotItems("4/07/2008").Visible = False
.PivotItems("5/07/2008").Visible = False
.PivotItems("7/07/2008").Visible = False
.PivotItems("8/07/2008").Visible = False
.PivotItems("9/07/2008").Visible = False
.PivotItems("10/07/2008").Visible = False
.PivotItems("11/07/2008").Visible = False
.PivotItems("12/07/2008").Visible = False
.PivotItems("14/07/2008").Visible = False
.PivotItems("15/07/2008").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Created Date")
.PivotItems("16/07/2008").Visible = False
.PivotItems("17/07/2008").Visible = False
.PivotItems("18/07/2008").Visible = False
.PivotItems("19/07/2008").Visible = False
.PivotItems("21/07/2008").Visible = False
.PivotItems("22/07/2008").Visible = False
.PivotItems("23/07/2008").Visible = False
.PivotItems("24/07/2008").Visible = False
.PivotItems("25/07/2008").Visible = False
.PivotItems("26/07/2008").Visible = False
.PivotItems("28/07/2008").Visible = False
.PivotItems("29/07/2008").Visible = False
.PivotItems("30/07/2008").Visible = False
.PivotItems("31/07/2008").Visible = False
.PivotItems("1/08/2008").Visible = False
.PivotItems("2/08/2008").Visible = False
.PivotItems("4/08/2008").Visible = False
.PivotItems("5/08/2008").Visible = False
.PivotItems("6/08/2008").Visible = False
.PivotItems("7/08/2008").Visible = False
.PivotItems("8/08/2008").Visible = False
.PivotItems("9/08/2008").Visible = False
.PivotItems("10/08/2008").Visible = False
.PivotItems("11/08/2008").Visible = False
.PivotItems("12/08/2008").Visible = False
.PivotItems("13/08/2008").Visible = False
.PivotItems("14/08/2008").Visible = False
.PivotItems("15/08/2008").Visible = False
.PivotItems("16/08/2008").Visible = False
.PivotItems("18/08/2008").Visible = False
.PivotItems("19/08/2008").Visible = False
.PivotItems("20/08/2008").Visible = False
.PivotItems("21/08/2008").Visible = False
.PivotItems("22/08/2008").Visible = False
.PivotItems("23/08/2008").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Created Date")
.PivotItems("24/08/2008").Visible = False
.PivotItems("25/08/2008").Visible = False
.PivotItems("26/08/2008").Visible = False
.PivotItems("(blank)").Visible = False
End With
hope i can get some assistance with this.
Locked. Title not expectable
Aloha,
I have a sheet where I can controll a pivot table with two buttons on the sheet. I can choose wich week I want to show in the table with a spinbutton. I also have two option buttons that let me choose if I want to se a singel week och accumulate a number of weeks in the table.
I need some help making some form of loop or something that will shorten the code below. (since I would like to have 52 weeks the code will be huge if I do it like below).
This is my code so far, I have only 6 weeks.
Code:
Sub Makro1()
Application.ScreenUpdating = False
Dim Singleweek_or_Accumulated As Integer
Dim Week_number As Integer
Singleweek_or_Accumulated = Range("J27")
' if J27 is = 1 the I will show singel week in the table
' if J27 is = 2 the I will show accumulated weeks in the table
Week_number = Range("J29")
' J29 is the cell where i select the week number
If Singleweek_or_Accumulated = 1 Then
' singel week scenario
With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week")
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = True
.PivotItems("14").Visible = True
.PivotItems("15").Visible = True
End With
With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week")
Select Case Week_number
Case Is = 10
.PivotItems("10").Visible = True
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
Case Is = 11
.PivotItems("10").Visible = False
.PivotItems("11").Visible = True
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
Case Is = 12
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = True
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
Case Is = 13
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = True
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
Case Is = 14
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = True
.PivotItems("15").Visible = False
Case Is = 15
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = True
End Select
End With
End If
If Singleweek_or_Accumulated = 2 Then
' accumulated weeks scenario
With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week")
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = True
.PivotItems("14").Visible = True
.PivotItems("15").Visible = True
End With
With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week")
Select Case Week_number
Case Is = 10
.PivotItems("10").Visible = True
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
Case Is = 11
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
Case Is = 12
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
Case Is = 13
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = True
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
Case Is = 14
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = True
.PivotItems("14").Visible = True
.PivotItems("15").Visible = False
Case Is = 15
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = True
.PivotItems("14").Visible = True
.PivotItems("15").Visible = True
End Select
End With
End If
End Sub
I need to know how to make a one size fits all selection for my pivot tables. The overall situation is; I have 12 regions, each of which has an Expense Report. I want to open each workbook, and configure the pivot tables the same. The problem I'm getting into is in regards to the "Strategy". I only want 11001and 11002 to show. All regions have these. However, there are tons of other strategies, and the mix differs by region. If have these these turned (as shown in my code below), I get an error if the value doesn't exist. If I don't turn them off, all show. How do I work around this?
Code:
ActiveSheet.PivotTables("PivotTable1").PivotFields("MFR_STRATEGY").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MFR_STRATEGY")
.PivotItems("11000").Visible = False
.PivotItems("11003").Visible = False
.PivotItems("11005").Visible = False
.PivotItems("11007").Visible = False
.PivotItems("11009").Visible = False
.PivotItems("11010").Visible = False
.PivotItems("11012").Visible = False
.PivotItems("11100").Visible = False
.PivotItems("11500").Visible = False
.PivotItems("11900").Visible = False
.PivotItems("13300").Visible = False
.PivotItems("17700").Visible = False
.PivotItems("18000").Visible = False
.PivotItems("19000").Visible = False
.PivotItems("20000").Visible = False
.PivotItems("20200").Visible = False
.PivotItems("20400").Visible = False
.PivotItems("20600").Visible = False
.PivotItems("21000").Visible = False
.PivotItems("21100").Visible = False
.PivotItems("22600").Visible = False
.PivotItems("23200").Visible = False
.PivotItems("23300").Visible = False
.PivotItems("25000").Visible = False
.PivotItems("25300").Visible = False
End With
Hi All. I tried creating a macro that generates a pivot table and sticks it in a new tab when a button is pressed. It sort of worked but had issues. So here is what I am trying to accomplish:
1. Push a button on a "GUI" tab to create the pivot table (ie, the button is on another tab than the data and eventual pivot table)
2. Old pivot table is deleted and new is created in the "Pivot" tab based on the data in the "Data" tab.
3. Row fields are "Preferred Supplier", "M-spec", and "PO or Plan".
4. Column Field is "Due Date".
5. Data field is "OpenQty".
6. Date must be grouped by month
Here is the code I have that is creating the table in a new (random) tab. Problem is, I can't figure out how to specify which tab to create the table on, and I can't figure out how to group by month.
Code:
Sub Pivot2()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("PO")
Dim WSR As Worksheet
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable("", TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row fields
PT.AddFields RowFields:=Array("M-Spec", "Prefered Supplier", "PO or Plan"), ColumnFields:="DueDate"
' Set up the data fields
With PT.PivotFields("OpenQty")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
'.NumberFormat = "#,##0"
End With
' Ensure that you get zeroes instead of blanks in the data area
PT.NullString = "0"
' Calc the pivot table to allow the date label to be drawn
PT.ManualUpdate = False
PT.ManualUpdate = True
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub
Hello,
I need to write a macro for a workbook with 1 sheet where i need to go to
data --> then select Group/outline ---> then select group
when the window opens in the start date it needs to get the latest date from the column "C" and the year from Column "A".
and end date should be 4 months from the start date.
Then select years and click ok.
Then save this workbook as "WeeklyClosingsReports and date(date should be the start date)"
date format: "mm/dd/yy".
Following is the code i get when i record this macro.
Code:
Sub clsrep()
Selection.Group Start:=39895, End:=40017, Periods:=Array(False, False, _
False, False, False, False, True)
End Sub
Please Help!
Thanks in advance.
Configuration: Windows Vista
Internet Explorer 7.0
Excel 2003
I have this formula, which works
Code:
=IF(AND(MONTH(A1),DAY(A1))=AND(DAY(TODAY()),(MONTH(TODAY()))),"True","False")
When adding ISERROR to it, it doesn't work... The result is TRUE when it should be FALSE. I have spent about two hours on it trying different variations and Googling for assistance to no avail
Code:
=IF(ISERROR(AND(MONTH(A1),DAY(A1))=AND(DAY(TODAY()),(MONTH(TODAY()))))," ",IF(AND(MONTH(A1),DAY(A1))=AND(DAY(TODAY()),(MONTH(TODAY()))),"TRUE","FALSE"))
Guidance is GREATLY appreciated...
Thank you.
I have what appears to be a simple script that's not working correctly. The code below uses data from the spreadsheet "ESSData" to create a pivot table. Everything works well, except for the fact that I DON'T want blanks in my pivot. I've tried to get rid of the blanks by using
Code:
PivotItems("(blank)").Visible = False
, as that was what Excel used when I recorded the creation of the pivots. However, it doesn't seem to work. No error, it just doesn't do anything. I'd appreciate some help on this one.
Thanks!
Code:
'Create the Pivot
DstWkb.Worksheets("ESSData").Activate
Columns("A:D").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Range("A1:D200").Address(, , xlR1C1)).CreatePivotTable TableDestination:="" _
, TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2")
.ColumnGrand = False
.NullString = "0"
End With
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Func Code-ESS", "Posn Func Title-ESS"), PageFields:="PAC-ESS"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Reg 1")
.Orientation = xlDataField
.Caption = "Sum of Reg 1"
.Function = xlSum
End With
Range("A4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Func Code-ESS").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Func Code-ESS").PivotItems("(blank)").Visible = False
End With
Range("B4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Posn Func Title-ESS").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Posn Func Title-ESS").PivotItems("(blank)").Visible = False
End With
Hi everyone,
Once again I need your help...
I create the following macro
Code:
With ActiveSheet.PivotTables("PivotTable3").PivotFields("OAs")
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
(...)
.PivotItems("51").Visible = False
.PivotItems("(blank)").Visible = True
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("OAs")
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
.PivotItems("7").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
(...)
.PivotItems("50").Visible = False
.PivotItems("51").Visible = False
.PivotItems("(blank)").Visible = True
End With
And I want to know if it is possible
a) when I open the worksheet, the pivot table show only the item (blank) - via Sub Auto_Open()
b) if I add a new item I do not need to actualize the macro
Thanks in advance
|
|
