Macro To Pick Up A Range Of Date To Filter On In A Pivot Table

Jun 25, 2008

When using a Pivot Table I regularly filter on known data specific to me i.e. WBS codes.

Column A contains the WBS Codes which can be as many a 1000, however, I only need to filter out the 10 or so I require, and currently I uncheck all the records and manually scroll through the list to check the ones I require.

In order to speed things up I recorded a macro which works fine, however, If I need to check any new codes I would have to record the macro again, as I tried to edit the Macro and all it does is to un check all the other codes I don't need.

Is it possible to create a Macro that picks up a range of data that is then used to filter on. (i.e. only the records I require)

View 9 Replies


ADVERTISEMENT

Date Filter Display In Pivot Table

Jun 16, 2014

I have a question with regards to the display of the Date filter in a pivot table I am making.

As it stands, the filter function is displaying like this : date 1.JPG

I am looking for the filter to look more like this : date 2.JPG

I believe that I have all the dates in the source table formatted as "Date".

View 4 Replies View Related

Filter Pivot Table In Attachment By Posted Date?

Feb 18, 2014

How to filter the pivot table in the attachment by the Posted date. I only want to look at the data for dates within 30 days of today, and can't figure out how to do that manually, much less programatically. The attachement is a small sample of a huge report I receive; all I get is the pivot, which contains tons of information. I filter it down by my various criteria, but that's still hundreds of thousands of lines. Limiting to the last 30 days can make a huge difference, but I can't find the trick to it.

Example.xlsx‎

View 14 Replies View Related

Controlling Date Filter On Pivot Table With Dropdown

Apr 22, 2014

I have a table with business data such as units sold, revenue etc. entered by date (1/1/14, 1/2/14...) for 4 different business units. I have this data summarized in a pivot table. The pivot table is set up with the date filter as a row label. I would like to have a consolidated report on another worksheet with a drop down menu being used to control the date filter on my pivot table.

The following items would be on the drop down:

Yesterday, Month to Date, Quarter 1, Quarter 2, Quarter 3, Quarter 4 and Year to Date.

View 1 Replies View Related

Pivot Table - Grouping Items (Date) In Report Filter?

Nov 11, 2013

I have a pivot table with the following items;

Report Filter = Project Names & Dates (filtered on 2 fields)

then the pivot table of data shows
Dept, Sum of Hours, Sum of Total ($), Sum of Days

When you click on the drop down to adjust the filter for the Date it gives you the whole list of dates, day by day as it is in the source data.

Is there a way to make it in the filter by month and year, the way it would in an ordinary filter. So if I wanted to have the pivot table show only the values with an October date I can, rather than having to deselect all and then individually check the boxes for the 1st through to the 31st of October.?

View 2 Replies View Related

Filter Pivot Table Date Column By Labels Using Data In Other Cells

Jul 16, 2014

On a sheet called, "Details", I have a pivot table that has three fields in the column area, Calendar type, Description, and Dates. I want to filter the pivot table based on a label filter in the Dates column. The filter should be between two dates (in D4 and D5) that are entered on another sheet and passed to the Details sheet through formulas in cells D4 and D5.

I have attached the following code to a button on a different sheet.

The code successfully filters for the employee name (which is a report filter in the pivot table) which is in a named cell.

I am having trouble with passing the start and end dates to my pivot table filter. I do not get any errors, the filter is simply blank.

Private Sub CommandButton3_Click()
Sheets("Details").Select
Dim pt As PivotTable
Dim Employee As String
Dim SDate As String
Dim EDate As String

[Code]...

View 6 Replies View Related

Filter OLAP Pivot Table Based On Named Range In Another Worksheet

Mar 14, 2012

I have a named range of values on Sheet2 (GPI). Sheet1 is an OLAP pivot table containing row label (GPI 14) and values (Net Rx Count) only.

Unfiltered this list is over 7,000 rows. I need VBA code to display only those rows where the GPI 14 value matches any value in the named GPI range on Sheet2.

In other words how can I display the select rows without manually selecting the items of interest AND without manually hard coding the values in the code as they will change.

Below is 1 of the many codes I tried. This appears to be the most intuitive but I get an 'invalid procedure" error at Set my PivotTable...

'Sub PivotAnalysis()
'
Dim myPivotTable As Excel.PivotTable
Dim myPivotField As Excel.PivotField
Dim myPivotItem As Excel.PivotItem

[Code]....

View 4 Replies View Related

Macro To Filter Item Resulting In Empty Pivot Table

Dec 28, 2012

I am having an issue with my macro code. Basically I am trying to filter a pivot table by selected criteria. The code seems to be correctly filtering the pivot table but the table appears with no data in it unless I manually refresh the table. This defeats the purpose of automating with a macro. I have attempted a "work around" of just refreshing the pivot table after the filtering is complete but that was unsuccessful. Here is the macro code:

Sub BigReportMacro()

Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As PivotField
Dim E

Sheets("Pivots").Visible = True
Sheets("Pivots").Select

'Refreshing all the Pivot Tables

[Code] .....

View 9 Replies View Related

Excel 2010 :: Changing Pivot Table Report Filter Via VBA With Named Range Or Array

Jul 21, 2011

I have a set of four pivot tables on a sheet that I need to programmatically change a Report Filter (Page Field) so I can create sets of reports in an automated fashion. This will be the first step in that process. The change will involve choosing > 1 Role each time the code loops through based on Named Ranges I've defined that are associated with that Role.

My code thus far:

Code:
Sub TestCode()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

[Code]....

emm_dc_gsr is one of many Named Ranges that will contain a variable number of elements. Just using the one right now to see if I can get the code to work, I'll eventually make another Named Range/Array of all them so I can loop through each Report ("ReportPick").

I want the Report Filter to consult that Named Range for its values and apply those values to PivotField "Role" that is used as a Report Filter.

When running this code above, I get a "Role" Field that says "All" but no values (the table is completely blank), with no evidence as to why it'd be blank (all filters in every Report, Column and Row are working normally and are filled in). When I choose a value manually after the code is run, the pivot table values populate. Do I need to somehow index the Named Range in that loop? I'm just confused about this step right here:

For Each pi In pf.PivotItems
If pi.Value = RolePick Then
pi.Visible = True
Else: pi.Value = False

When I've run other versions of the code, I've gotten an array version of it to "work" using LBound and UBound, but it never chooses the right two values even though those are verified as stored in the array via a pass-through. It chooses the first few values in the Report Filter.

Here's the corresponding code for that:

For i = LBound(myArray) To UBound(myArray)
pf.PivotItems(i).Name = myArray(i, 1).Value
pf.PivotItems(i).Visible = True
Next

I do not care if I use an array or a Named Range. I just want something that is simple and works. Passing the values directly from the named range seems easiest to my brain, but I'm open to anything and I'm clearly missing something (probably silly).

I also have no idea why " .AutoSort xlManual, .SourceName, .EnableMultiplePageItems" is necessary though every piece of sample code I've seen seems to have some variation of it.

(Using Excel 2010, Windows 7.)

View 9 Replies View Related

Allowing User To Pick CSV File To Source Pivot Table

Feb 21, 2012

I am sourcing data for my pivot table from an csv file. The ultimate task I am trying to accomplish is to allow the user upon hitting a button to select the file that would feed the pivot table. The code below is what I got by linking the initial source file to the pivot table. As I can see the source file named "4 Port.csv" is listed in the SQL-like statement. How do I go about allowing the user to pick the file from the "open file" dialog?

Code:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DefaultDir=C:Documents and Settings2301Desktop;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferS" _

[Code].....

View 9 Replies View Related

Pivot Table Grouping - Range To Show Last Date Only

Oct 23, 2013

Have a simple Pivot table that i have grouped in 14 day buckets.

Currently, the date range looks like this:

1/21/2013 - 2/3/2013

However, I want the range to show the 'last' date only.

Is there a simple way to do this? I just can not see it in the format cells menu...

View 2 Replies View Related

How To Compare Percentage Difference Between Date Range Through Pivot Table

May 9, 2014

I would like to compare % difference between date range through pivot table

Date RangeProductAmt
1st weekABC1000
2nd WeekABC2000
3rd WeekABC1259
1st weekXYZ3000
2nd WeekXYZ4000
3rd WeekXYZ2500

How to plot a Pivot table to compare the % difference between 3rd Week Vs 2nd Week Vs 1 Week for each product through pivot table?

View 2 Replies View Related

Combined VLOOKUP With A Date Range SUMIF From Pivot Table Data?

Feb 13, 2014

I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.

The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).

And to make things more complicated:

if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.

=IF(ISERROR(VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE)),0,VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE))

View 5 Replies View Related

Apply A Filter In A Pivot Table And Extract Results In A Table

Jul 24, 2008

I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.

View 9 Replies View Related

Macro To Increase Range Of Pivot Table?

May 2, 2013

i have a workbook containing two sheets "Impiorted Data" which is the data which is downloaded and the range will change each time the data is imported and "Pivot Table"

I would like a macro to increase the range of the data from col A to the lasst row in Col K so that the pivot Table has the correct range

I have recorded a macro , but would like this to be streamliined with regard to the range

ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C:My Documents[Debtors.xlsm]Imported Data!R1C1:R2797C11", _
Version:=xlPivotTableVersion14)

View 3 Replies View Related

Excel 2010 :: Macro To Select Latest Date In Dropdown Box Of Pivot Table?

May 9, 2013

I am trying to create a macro using the recorder that will select the latest date in a drop down list. The dates upate each day in the pivot table after the table is earlier refreshed. Using the recorder simply picks the same date each time. I want it to select the latest date. Am using Excel 2010 and have some very limited experience in vba for editing macros.

View 1 Replies View Related

Set Pivot Table Dynamic Source Range Macro Code

Apr 9, 2008

Ultimate goal is to automatically update the source data for 4 pivot tables that are on 1 worksheet. The data for those 4 pivot tables are on 4 different worksheets w/in the same workbook. Consolidating into one dataset is not an option.

I'm familiar with a dynamic named range, but the 4 worksheets that contain the data are replaced daily via automated Access output which creates an error.

I know how to do this adhoc by matching the pivot table names with their respective worksheets, but there are many other documents with similar layouts where this would beneficial.

Below is an example of how I currently update 3 pivots on separate worksheets w/ the same data range which is w/in the same workbook. My proposed changes are below the current. Any ideas on how to return the workbook name as a string...Or am I going about this the completely wrong way...

View 8 Replies View Related

Change The Date On One Of The Pivot Table And Pivot Table Match

Apr 29, 2006

I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.

View 2 Replies View Related

VBA Pivot Table Filter

Apr 6, 2013

I am trying to filter onto Date field in my pivot table with a start date and an end date with VBA (please find code below).

However, my code single makes all pivot items invisible.

Bascially, in cell B1, I have my start date i.e. 05/04/2013 and end date in cell C1. I would like to filter out only items within these dates.

VB:
Sub PTFilterTest()

Dim ptPvt As PivotTable
Dim ptFld As PivotField
Dim i As Long
Dim dtBegin As Date
Dim dtEnd As Date

[Code] .....

View 2 Replies View Related

VBA - Pivot Table Filter Set?

Oct 23, 2012

I'm trying to have a command button clear all filters on a pivot table (PivotTable1) and apply a new filter equal to a cell value (C4). If the cell value can't be found I want the filter to equal "No Meeting". I'm trying to recycle the code from another workbook I built a long time ago, I just can't get it to convert to this application.

Code:
Dim County As IntegerDim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem

[Code]....

View 1 Replies View Related

Pivot Table Filter

Nov 6, 2008

I have a list of names and a count column, So it counts how many times the names have appeared from my raw data. Simple.

Using excel 2003 what i would like to do is filter the pivot table so it only shows people with a >1 count.

Now in excel 2007 this is really easy and i can just apply a >1 filter. I can't work out what to do in excel 2003.

View 9 Replies View Related

Using Multiple Filter For Pivot Table

Aug 2, 2012

I have a huge excel file. This file contains Projects, Project Manager, departments and sales for different quarters. Each department has sales and the quarter the sale was done. Now I want to create a pivot table where I can see the sales for each quarter for each project or each project manager. Attached file may elaborate the problem. ShaA1.xlsx

View 9 Replies View Related

Filter First X Items Of Pivot Table?

Oct 26, 2013

I've created a PivotTable using VBA that contains hundreds of PivotItems, which would look bad when a PivotChart is made.

I'd like to set the PivotTable to make visible only the first X items (let's say 10). How would I do this in VBA?The macro recorder gives me the name of the PivotItem, but this varies so I'd like to use an index:

VB:
ActiveSheet.PivotTables("PivotTable4").PivotFields("UWI").PivotItems("Item1").Visible = False
ActiveSheet.PivotTables("PivotTable4").PivotFields("UWI").PivotItems("Item2").Visible = False

View 2 Replies View Related

VBA To Capture Filter Value From Pivot Table

Jun 23, 2014

I want my report title is more dynamic which is based on the pivot table's <Status Date> filter. It mean if I select the <Status Date> filter option as 6-23-2014. Then the report title will automatic update as "Statistic Report 6-23-2014" instead of I have to change the date each time I run the report.

View 1 Replies View Related

Looping Through Pivot Table Filter?

Aug 19, 2014

I am trying to loop through each pivot item in the pivot table filter called 'COMPANY_NAME'

This is the code I have so far but get an error on line 'PvItemL.Visible = False'

[Code]......

View 6 Replies View Related

VBA Pivot Table Filter Using EOMONTH

Oct 12, 2011

I have this code for setting a filter on my pivot table to show all data occuring after a certain date. Is there a way to add code so that instead of it saying "Value1:="31/10/2011"" that it would work out the last day of the current month and use that.

Something like:

Code:
=EOMONTH(TODAY(),0)

Code:
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date_CDD").PivotFilters. _
Add Type:=xlAfter, Value1:="31/10/2011"
Sheets("SRS Pivot").Select

View 2 Replies View Related

Filter Pivot Table On Property Via VBA?

Oct 20, 2011

I need to filter a pivot table based on a field property. Sort of strange, but the database I get the PivotTable data from returns the Week in the accounting month. as the field name. It has several properties which I have figured out how to expose including the period end date. Now I need to figure out how to filter the PT based on that.

The following shows the property:

Code:
ActiveSheet.PivotTables("PivotTable4").CubeFields("[Time]").AddMemberPropertyField Property:="[Time].[Week in Month].[End Date]

Now if I just knew how to filter with that value I could move on the the next issue ....

View 2 Replies View Related

Auto Filter Pivot Table?

Mar 8, 2012

I have a summary worksheet, which has a drop down cell. on another tab I have a few pivot tables, is there a way I could get one of the pivot tables to auto filter based on the selection in the summary worksheet?

View 1 Replies View Related

Pivot Table Filter On Both Label And Value?

Apr 24, 2012

I have a pivot table, where the rows are product names, and there are two column fields: quantity and money.

I want to filter the pivot table to only show data where the amount of money equals zero. This is easily done with a Value Filter.

I also want to filter the pivot table to only show data as applicable to certain product names. This is easily done with Label Filter.

However, I cannot get both of these filters to stick at the same time. When I add one, the other is removed.

View 4 Replies View Related

Array For Filter In Pivot Table?

Dec 11, 2012

How to use an array of multiple values ​​in code I want to use the array of values "jan" "feb" "mar" in the code instead of "jan"

Code:
Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("Pivottable1").PivotCache.Refresh
ActiveSheet.PivotTables("Pivottable1").PivotFields("cat").ClearAllFilters
ActiveSheet.PivotTables("Pivottable1").PivotFields("cat").PivotFilters.Add _
Type:=xlCaptionDoesNotEqual, Value1:="jan"
End Sub

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved