Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

How Do I Pull The Current Month For Use In Pivot Table Start Date?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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)




View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi

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