Pivot Table Page Fields Selection

Hi,
IN VBA how can I select only the data I need in the Pivot Table Page section.
Need to get only the data <45 in column "Days"
"Days" is in the Page Section of the pivottable.

The user will only click a button to answer different questions, and 
generate a new data sheets base on is criterias and then, the pivot table is 
refresh. the user will not select any other data.Most of the macro is ok. My 
problem is only selecting the <45.
On the Page fields the pivot table should always select "DAYS" <45. from the 
data.
I tried different macro and I can't find the solution.
I found something usefull on the newsgroups however when the macro do not 
see any data smaller than 45  it stop with an error message.
Please help. Thanks
0
Utf
11/16/2009 4:24:02 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
2548 Views

Similar Articles

[PageSpeed] 1

might be hard to decipher, but generally:

1. set cubefield to enable multiple page items
2. decide whether the pivot is 2003 or 2007 version (not application
version, pivot version)
3. depending on version create array and set to visbile page items
(2007) or iterate through items and add (2003)

hope it helps.


Private Sub SetCurrency_Pivot(ByRef pvtTable As PivotTable, ByVal
strCurrency As String, ByVal enmPivotVersion As EPivotVersion)

On Error GoTo ErrorTrap

    Dim pvfCurrent As Object

    With pvtTable

        For Each pvfCurrent In .PageFields

           If pvfCurrent.Name =3D "[Report Currency].[Report Currency].
[Report Currency]" Or pvfCurrent.Name =3D "[Report Currency].[Report
Currency]" Then

                .ManualUpdate =3D True
                If .CubeFields("[Report Currency].[Report
Currency]").EnableMultiplePageItems =3D False Then
                    .CubeFields("[Report Currency].[Report
Currency]").EnableMultiplePageItems =3D True
                End If

                If enmPivotVersion =3D EPivotVersion.Pivot2007 Then

                    .ManualUpdate =3D True
                    pvfCurrent.VisibleItemsList =3D Array("")

                    If strCurrency <> "NULL" Then
                        .ManualUpdate =3D True
                        pvfCurrent.VisibleItemsList =3D Split
(ReturnMultiValueString("[Report Currency].[Report Currency].&[",
strCurrency), ",")
                    End If

                ElseIf enmPivotVersion =3D EPivotVersion.Pivot2003 Then

                    If strCurrency <> "NULL" Then
                        .ManualUpdate =3D True
                        SetPageFields_MultiValue pvtTable, "[Report
Currency].[Report Currency]", "[Report Currency].[Report Currency]",
strCurrency
                    End If

                End If

           End If

        Next pvfCurrent

    End With

ExitSub:

    If IsObject(pvfCurrent) Then Set pvfCurrent =3D Nothing

    Exit Sub

ErrorTrap:

    GoTo ExitSub

End Sub


Private Function ReturnMultiValueString(ByVal strPrefix As String,
ByVal strValues As String) As String

On Error GoTo ErrorTrap

    Dim varStrings As Variant
    Dim intArrayCounter As Integer
    Dim strReturnString As String

    varStrings =3D Split(strValues, ",")

    For intArrayCounter =3D 0 To UBound(varStrings)

        strReturnString =3D strReturnString & Trim(strPrefix) & Trim
(varStrings(intArrayCounter)) & "],"

    Next intArrayCounter

    ReturnMultiValueString =3D Left(strReturnString, Len
(strReturnString) - 1)

ExitFX:

    Exit Function

ErrorTrap:

    GoTo ExitFX

End Function

Private Function SetPageFields_MultiValue(ByRef pvtTable As
PivotTable, ByVal strPageFieldTop As String, ByVal strPageFieldDetail
As String, ByVal strValues As String) As Boolean

On Error GoTo ErrorTrap

    Dim varStrings As Variant
    Dim intArrayCounter As Integer

    With pvtTable

        varStrings =3D Split(strValues, ",")

        For intArrayCounter =3D 0 To UBound(varStrings)

            .ManualUpdate =3D True
            If intArrayCounter =3D 0 Then

                .PivotFields(strPageFieldTop).AddPageItem
strPageFieldDetail & ".&[" & varStrings(intArrayCounter) & "]", True


            Else

                .PivotFields(strPageFieldTop).AddPageItem
strPageFieldDetail & ".&[" & varStrings(intArrayCounter) & "]"

            End If

        Next intArrayCounter

    End With

    SetPageFields_MultiValue =3D True

ExitFX:

    Exit Function

ErrorTrap:

    GoTo ExitFX

End Function


On Nov 16, 11:24=A0am, Mouimet <Moui...@discussions.microsoft.com>
wrote:
> Hi,
> IN VBA how can I select only the data I need in the Pivot Table Page sect=
ion.
> Need to get only the data <45 in column "Days"
> "Days" is in the Page Section of the pivottable.
>
> The user will only click a button to answer different questions, and
> generate a new data sheets base on is criterias and then, the pivot table=
 is
> refresh. the user will not select any other data.Most of the macro is ok.=
 My
> problem is only selecting the <45.
> On the Page fields the pivot table should always select "DAYS" <45. from =
the
> data.
> I tried different macro and I can't find the solution.
> I found something usefull on the newsgroups however when the macro do not
> see any data smaller than 45 =A0it stop with an error message.
> Please help. Thanks

0
james
11/16/2009 4:43:10 PM
Reply:

Similar Artilces:

Selecting or deleting data
Hi all Is it possible to have a formula which recognises whether to select o delete data. Eg; A1 = xxx A2 = yyy A3 = zzz & C1 = (Nothing) C2 = (Nothing) C3 = zzz The results in E1,2 & 3 should have data in A1 and A2 but not A3 or C because Column C is for cancelled items and if it data in this colum matches then they should cancel each other out. Many thanks Joe -- Message posted from http://www.ExcelForum.com Hi try in E1: =IF(A1=C1,"",A1) and copy this down >-----Original Message----- >Hi all > >Is it possible to have a formula which recognises whether...

Cannot Delete rows in AsyncOperationBase table (MSCRM DB is 73GB on SBS)
In my ignorance, I let the MSCRM database grow to a huge size. I became alarmed at 58GB and 2 days later it was 73GB. With the help of my solutions partner, we found out that we have to manage the size of my AsyncOperationBase table by deleting old completed rows. The scripts always timeout. Then I decided to try and delete just one row using SQL Management Studio. Here is the error. No rows were deleted. A problem occurred attempting to delete row 1. Error Source: .Net SqlClient Data Provider. Error Message: Timeout expired. The timeout period elapsed prior to completion of the operati...

ScrollIntoView Non-Selected Cell ..?
How do i.. Without selecting it.. ScrollIntoView a Cell.. Say... R120C140 ...? ActiveWindow.ScrollintoView .. but how to find the Document Coords in Pixels of that Cell ..? when Rows/Columns are uneven sized ..? Any Feedback would be Appreciated.. Andrew ;-) sub dk() ActiveWindow.ScrollRow = 120 ActiveWindow.ScrollColumn = 140 End Sub This would put row 120, column 140 in the upper left corner of the screen. "Andrew" <NoToSpam@ReadItYourSelf.com> wrote in message news:BeydnRXlerMJfhLWnZ2dnUVZ8gOdnZ2d@bt.com... > How do i.. >...

automating a pivot table
When using a pivot table, is there any way of automatically selecting from a drop down menu. By this I mean if I can select one of 5 colours, If I put "red" in cell A1, can the pivot table be made to take the value in cell A1 as the colour selection, rather than me selecting red from the drop down list. thanks in advance You can add fields to the page area. When you select from the dropdown list in a field there, the table is filtered to show only that item. bobf wrote: > When using a pivot table, is there any way of > automatically selecting from a drop down menu. &g...

Using Form to update table
Hi I am a little lost. I have a form which has a subform. In the header of the form there is a combo box to selete numbers and a text box to enter a date (not current date) On the subform I have 2 text boxes which onces the 2nd textbox is populated another set is to come underneath ready for completion (continues form). On the main forms footer I have an update button which is to perform a task. What I am lost with is how can I get the subform to work as a continues form so once the 2nd text box is completed another set is ready for entering. and How do I then click on the button to fi...

Deleting unicode fixup table
One of our clients has an Exchange 2003 Std SP1 installation that just bumped up against the 16Gb limit. We had them do some house cleaning and did the 17Gb hack to get them temporary extra space which got the system back on an even keel but when we tried to do the offline defrag to compress their database the result showed us it was corrupt and failed about 10% into it. The store mounts and runs fine and backs up OK but we are unable to defrag and now we have concerns that bad things are on the horizon. I know everybody says the best thing to do is a restore from b/u if at all possible and ...

Pivot Table based on external (.mdb) database
Can you point me to an article/tutorial regarding creation of Pivot Tables based on imported .mdb data files? Thanks! Hi, Try http://edferrero.m6.net/tutorials.html Ed ferrero "KG" <KG@discussions.microsoft.com> wrote in message news:12CA98CF-C52D-45BD-AF7B-7EC527C5FB18@microsoft.com... > Can you point me to an article/tutorial regarding creation of Pivot Tables > based on imported .mdb data files? > > Thanks! Perfect! I'll need to do a little studying... "Ed Ferrero" wrote: > Hi, > > Try http://edferrero.m6.net/tutorials.html &g...

Combining two fields into one in a query for a report
The database I am working on is to keep track of parts that are either in their storage area (on a shelf) or on an assembly line. I have two fields, one called Shelf and every part has a shelf assigned to it. The other field is Line and if a part in on an assembly line, there is a number indicating that line, if the part is on it's shelf, the word STOCK appears in the line field. (Has to be this way for the rest of the database to work, so this is not up for question) Now, I need to creat a report to show where all parts are. If they are on a line the result needs to show the l...

tables that link manufacturing to SOP
Hi All, I have recently joined a company that is using the manufacturing module in Great Plains Dynamics. While I have very familiar with all the standard tables ( especially the SOP30200 and SOP30300) having designed many Crystal Reports for them, I cannot seeme to find the correct way to link an order to a manufacturing order. I have found the IS010001 - ICON Sales Order Line, and the IS030001 - ICON Sales Order Line History files. The issue I am having is that these 2 files seem to replicate not only the manuafactured item, but also any other lines on that order. So am I using th...

Excel 2002 Pivot Table Protection
I've been playing around with protecting my pivot table - so far, I can't get it quite right. What I'm hoping to do is allow users to refresh the pivot table and update any of the "page" dimensions, but NOT allow anyone to pivot or manipulate the row and column dimensions. Is this possible? You could use programming to restrict the pivot table use. For example: '========================================= Sub RestrictPivotTableExceptPage() Dim pt As PivotTable Dim pf As PivotField Set pt = ActiveSheet.PivotTables(1) With pt .EnableWizard = False .EnableDril...

Reports print wider than page
How can I get the reports to print on one page? even when I select landscape, it still goes over the border. I'd like to be able to print on portrait orienation; there's an aweful lot of wasted space in landscape mode. I'm running 2001 standard. Thanks! Most of the report customizations let you pick narrower formats. You can also pick a narrower font like Arial Narrow. "Greg Watson" <watsonsg@yahoo.com> wrote in message news:100101c37241$faeea390$a401280a@phx.gbl... > How can I get the reports to print on one page? even when > I select landscape, ...

simulating columns in rtf field on report
I am creating a system using Access 2007. I have a report which currently displays multiple RTF fields. Within one of the fields, I would like to print a 4-column 4-row table (including borders) to display some data. I need to do this in VBA as the data will vary. Does anyone know if this is possible. Even if I could get the data into a column format (without the borders), that would be an achievement. Any ideas would be appreciated. ...

Skip header on first page?
I have a multi page printout of a worksheet where I do not want the header to print on the first page. I know that in WORD there is a way to specify non-printing of headers and footers on the first page since it may be a title page or cover sheet. Is it possible to do the same in Excel? Hi Fred With code you can do it http://www.rondebruin.nl/print.htm#Header -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Fred615" <Fred615@discussions.microsoft.com> wrote in message news:E8FF30A8-5124-43E3-AE76-E2987D83A261@microsoft.com... >I have a multi page printout...

Select/Append Query Malfunction
I have an append query that takes a parameter and two combo box selections from frmEmpSetHours. One of the combo box selections is Shift (1, 2, or 3). The resultant dynaset is appended to tblEmployeeProduction. Shift 1 and 2 have Shift.ShiftHours of "8". Shift 3 has Shift.ShiftHours of "7.5". The problem is that the dynaset returned/appended after inputting selections for 3rd shift return a Shift.ShiftHours value of 8 instead of 7.5. I have tried building the sql from scratch one line at a time and have been successful returning the appropriate Shift.ShiftHours until I ...

Compare two table
Hi, I had transfer a table from the server database to my local computer. Is it possible if use excel to compare those two tables and extract the missing records that were not copy out? regards, ray -- ray5_83 ------------------------------------------------------------------------ ray5_83's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29800 View this thread: http://www.excelforum.com/showthread.php?threadid=495113 Maybe... Can you pick out a key field/column that is common to both? If you can, you can use some of the techniques that Chip Pearson descr...

Setting Value Of One Cell Equal To Value Of Selected Cell
Without using VBA - If cell A1=red, A2=white and A3=blue, can I set C1 to be the value of the selected cell so that if A1 is selected, C1 will equal red, if A2 is selected, C1 will equal white, etc.? Thanks, Sheldon Potolsky Sheldon, In a word, no. No worksheet thing comes to mind that works as a function of the active cell. You might want to use some radio buttons or a list box (Forms Toobar or Control Toolbox) instead of cell selection. They'll let you click to make such choices, and you can use formulas that will change a cell value as you wish. -- Regards from Virginia Beach, ...

a range area referred anywhere and repeatively on a new worksheet page
Hi I have a grid / table which has prices on which i wish to do calculations on so i entered the information and then named it as a range pricegrid (pricegrid refers to cells between (a1:d26)) I then create a new worksheet which i wish to refer to pricegrid on the same page. if i am refer to =pricegrid on the next worksheet and the rows and columns exactly aligns with the value everything works i now want to move to a14 and want to use =pricegrid it will show value as it cant display in a different area i presume i can use the offset function but how can i create value on ...

Stop entering data if a specific field is Null
Hi I have a table where there is one field named "Apartment" I have put required Yes for this field. Here is the problem and I am surelly not the first to ask that. Right now, user can fill all fields and when he wants to add a new record, system tells that the "Apartment" field must be entered unless the record will not be saved. To add a new record, user have to click a button. When he clicks, focus goes on the "Apartment" field. What I want is if user don't fill the "Apartment" field he will not be able to go to other fields...

Customize Investment Summary on 'My Money' Home Page?
Okay, so I've got a lot of stocks, mutual funds, etc., that I don't own anymore. Can anyone shed any light on how I would clear that clutter out of the Investment Summary on 'My Money' home page? Denise ...

OUTLOOK caused an invalid page fault in module SHLWAPI.DLL at 015f:70bf5a43
When I bring up Outlook 98 from Office97 Small Business Edition which I've been using for years I get the following message and Outlook closes down. OUTLOOK caused an invalid page fault in module SHLWAPI.DLL at 015f:70bf5a43. Registers: EAX=00000000 CS=015f EIP=70bf5a43 EFLGS=00010246 EBX=00428f30 SS=0167 ESP=0056f878 EBP=0056f890 ECX=0176789c DS=0167 ESI=00000000 FS=32b7 EDX=00428f44 ES=0167 EDI=00000000 GS=0000 Bytes at CS:EIP: 0f b7 06 46 46 83 f8 41 7c 08 83 f8 5a 7f 03 83 Stack dump: 70bf5a3a 70d9f263 00000000 00428f44 00000000 01767800 0056f8c4 70d269ec 00428f30 00000003 00428f4...

How to select No Button in AfxMessageBox
Hi there, just a short question. When I create a message box using AfxMessageBox with style MB_YESNO, how can I specify to select the No button and not the YES button? Thanks, Christian <chhenning@gmail.com> wrote in message news:1149620473.144818.209610@j55g2000cwa.googlegroups.com... > Hi there, just a short question. When I create a message box using > AfxMessageBox with style MB_YESNO, how can I specify to select the No > button and not the YES button? > Maybe use MB_DEFBUTTON2. -- Jeff Partch [VC++ MVP] Thanks, that was it!! ...

Order Table
HI, I have some questions regarding integrating web store with RMS... 1) Why can´t I see the "Order Table" when I query it in the Administrator? I have the structure but there is no PUBLIC_ equivalent and it returns a syntax error when I do a SELECT * From Order 2) What are the reference number and channel type fields in the "Order Table"? 3) What are the values for the Status field in the "Exchange Table"? In the docs I have only found that 1 means the order is processed. DOes 0 means it is not processed? are there other values? 4) I am integrating O...

In excel can you select certain cells which contain the same text
If I have several cells with the same text in them can I filter these out and select them. I know you can do this for formula etc but can it be done for text? try data>filter>autofilter -- Don Guillett SalesAid Software donaldb@281.com "ade" <ade@discussions.microsoft.com> wrote in message news:937AD9E1-668B-4E32-B194-29146DF0A60A@microsoft.com... > If I have several cells with the same text in them can I filter these out and > select them. I know you can do this for formula etc but can it be done for > text? ...

locking page layout but allowing data entry
I have a data sheet with merged cells, borders etc. I wish to allow data entry and mods including moving lines of data (control x) and pasting. At present this also removes merged cells and erases borders. Is there any way to create a locked format for a sheet which prevents users from changing cells, merges, borders etc, but still allows data to be moved without affecting the sheet; ie merged cells stay merged and drawn borders are not erased? Thank you for some expertise. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post use...

select items in a list box with a macro?
Instead of a mouse, can you select items in a list box with a macro? You can use a line like this: Me.ListBox1.ListIndex = 0 The first item in the listbox is item 0. If you allow multiple selections, then this worked ok for me: Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long With Me.ListBox1 For iCtr = 0 To .ListCount - 1 If iCtr = 0 _ Or iCtr = 3 _ Or iCtr = 5 Then .Selected(iCtr) = True Else .Selected(iCtr) = False End If Next i...