Updating Pivot Table from Datasheet

[adam]

Hi anyone one,

Sorry for posting a long question in this thread and I do apologize for any inconveniences.

I’m having a workbook with two sheets which I have named them as “Statistics” and “Database”.

In the sheet “Database” I have more than one rows with customer ID’s.

Suppose If a customer buys ten products I write ten rows of data in my sheet with the same customer ID. Likewise, if another customer buys five products five rows is filled with the Customer ID in the column parallel to the “Product Name” the customer has bought.

I have created a pivot table in the sheet “Statistics” with data taken from the sheet “Database”.

I have assigned a VB code to the pivot table so that it could filter sales form a given date range.

Now I’m stuck in finding a way to modify the pivot table so that it would have flexibility to show reports of;

Monthly statistics for the number of products bought by
1. Total Walk-in customers.
2. Total Walk-out customers.
3. Total number of products bought by walk-in customers.
4. Total number of products bought by walk-out customers.
5. Total number of fruits bought
6. Total number of vegetables brought.

For further description of my question, I have attached the workbook for your reference.

I don’t know whether all these is possible with an excel pivot table but any help would be kindly appreciated.

Thanks in advance.

[HansV]

If you add the Customer Location field to the Report Filter area, you can easily filter only walk-in customers or walk-out customers.

Your pivot table already includes subtotals for fruits and vegetables (and for soft drinks).

[adam]

The VB code that I have applied; to filter selected date ranges does not work when the macro is run.

What might be the cause for this? or what might wrong have I done in coding. I replaced the line "For Each pi In pf.ProductName" with "For Each pi In pf.PivotItems". But still the macro isn't working.

Any recommendations plz??

[HansV]

This should be a lot simpler:

Code: Select all

Sub FilterPivotDates()
  With ActiveSheet.PivotTables("PivotTable1")
    ' Remove any existing filter
    .ClearAllFilters
    ' Add a filter for the date range
    .PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
      Value1:=CStr(Range("H1")), Value2:=CStr(Range("H2"))
  End With
End Sub

[adam]

Thanks Hans for the quick response & the magnificent code that did the magic trick that I was wanting my code to do.

[adam]

Following is a worksheet event code which should automatically refresh the pivot table on my worksheet “Statistics” when I click on the worksheet's tab or otherwise activate the worksheet.

But the code does not seem to work to my satisfaction.
Say; I add a category as electronics in the column “Product category” of my worksheet “Database” and add item name as “Walkman and fill rest of the row’s column with appropriate data and click the tab of the worksheet with the name “Statistics”, the Pivot table does not seem to get either refreshed or updated.

If I may ask, what may be the reason for this?

Code: Select all

Private Sub Worksheet_Activate()
     'If this worksheet is activated, refresh the pivot table
     'Change "Statistics" my sheet's name
     'Change "PivotTable1" my pivot table's name

Sheets("Statistics").PivotTables("PivotTable1").RefreshTable
     
End Sub

[HansV]

The code can be a bit simpler:

Code: Select all

Private Sub Worksheet_Activate()
  Me.PivotTables("PivotTable1").RefreshTable
End Sub

But if you add new rows to the source data, they will not automatically be included in the pivot table. The source range is fixed.
You can make it dynamic as follows:

1. Activate the Formulas tab of the ribbon.
In the Defined Names section, click Define Name.
Type a name, for example SourceRange, and set the Refers To box to

=OFFSET(Database!$B$9,0,0,COUNTA(Database!$B$9:$B$1048576),6)

The COUNTA function ensures that the range will contain all non-blank rows.
Click OK.

2. Click anywhere in the pivot table.
Activate the Options tab of the ribbon (under Pivot Table Tools).
Click Change Data Source in the Data section.
Enter =SourceRange in the Table/Range box, using the name you defined in step 1.
Click OK.
The pivot table will now adjust its source range automatically.

[adam]

Thank you for the help Hans. The pivot table now refreshes & updates like a charm. I'm happy with your instructions & the simplified code you had provided.

[adam]

I have placed Product Category & Product Name, in the Row label box, date in the column label box and Customer Location in values box.

In this set up I could view the data from the pivot table as I wish to view; except that I cannot sort out Walk-in & Walk-out customers.

I did put the Customer location in the Report Filter box but the pivot table does not display the numbers under range column box when I do that.

As a consequence I cannot view the figures of sales.

If I may ask is there any other alternative where I could view all this information from the existing pivot table with the existing layout.

I have to make monthly statistics report, meaning I’m trying to layout the pivot table so that it displays all the information in the printed sheet such as total number of customers, and out of them walk-in & walk-out.

And total number of products sold for both categories of customers. And details I have mentioned before in this post.

In the layout of the attached workbook, it displays the figures of sales for each day. But the figures aren’t matching with the sales.

Suppose, I have sold 01 apple on 5/01/2009 from the datasheet but the pivot table is telling me that I have sold 02 apples.

I did try to figure this out this and as far as I know this figure is coming from the last number of the customer ID.

Any suggestions are welcome to display the information I had requested before in my pivot table so that when printed it displays all the information.

Once again sorry for posting a long question.

I have attached the workbook for further justification of my question.

[HansV]

The problem is that you have "Sum of Sales No" in the Values box. This should be "Count of Sales No":
- Click on the dropdown arrow to the right of "Sum of Sales No" in the Values box.
- Select "Value Field Settings" from the dropdown menu.
- Select Count instead of Sum in the list.
- Click OK.

If you want to see the number for walk-in customers and walk-out customers at once in the pivot table, drag the Customer Location field to the Row Labels box.

If you place it above Product Category, you'll see the numbers for walk-in customers at the top, and those for walk-out customers at the bottom.

If you place it below Product name, the numbers for each product will be split into those for walk-in customers and walk-out customers.

[adam]

All of the suggestions and recommendations provided by you has helped to put up the data as I requested. But there's one last thing I'm unable to figure out. Total number of customers within a given date range. and out of them total walk-in and walk-out customers.

The data now gives the products bought by walk-in and walk-out customers.

What should I do if I want to display the total number of customers with separate walk-in & walk-out customers.

[HansV]

A pivot table counts the number of records (rows), so if there are 5 records for the same walk-in customer, this customer will count as 5, not as 1.
So you can't use the existing pivot table to count the number of distinct customers. You need a two-step process for that: use advanced filter to extract the unique customer types and IDs for the date range, and create a second pivot table based on the extraction range.
See the attached version.

Sample Pivot Table -2.xlsm

[adam]

Based on your sample workbook I have added a new sheet to my workbook with the name “Summary” and created a pivot table02 by the data taken from the sheet “Summary”.

The sheets “Database” and “Summary” gets updated simultaneously.

I have placed the Pivot table02 below the pivot table 01; and tried modifying the macro to filter date from both the Pivot tables as follows

Code: Select all

Sub FilterPivotDates()
  With ActiveSheet.PivotTables("PivotTable1")
   With ActiveSheet.PivotTables("PivotTable2")
    ' Remove any existing filter
    .ClearAllFilters
    ' Add a filter for the date range
    .PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
      Value1:=CStr(Range("H1")), Value2:=CStr(Range("H2"))
  End With
End Sub

But I’m getting an “End sub” error message when I run the macro.

The pivot table02 that I created has a “blank” column & a blank “row” where I couldn’t find the cause.

I would be happy if you could let me know what I had done wrong in modifying the code and why I’m getting a blank column and a blank row in my second pivot table.

I have attached the workbook for further reference.

[HansV]

You can't use a construction such as

Code: Select all

  With ActiveSheet.PivotTables("PivotTable1")
   With ActiveSheet.PivotTables("PivotTable2")

because the first With... would be invalidated.
Moreover, you don't want to filter the second pivot table - it is already based on filtered data. I provided code to update the source data in the workbook attached to my previous reply. Please study it more carefully.

You shouldn't place the second pivot table immediately below the first one - there would be no room for the first one to expand. Leave plenty of room between them.

You have moved the source data for the second pivot table to another sheet without adjusting the corresponding names. Please study the workbook that I mentioned. You can't just change things at random and expect them to keep on working correctly.

[adam]

Thanks for the suggestion Hans. I have now put up the second Pivot table in the sheet "Statistics" by hiding a couple of rows in between pivot table 01 and Pivot table 02. In this way when the pivot table 01 is updated by updating the "Datasheet" the message "Pivot Table cannot overlap Another Pivot Table" does not appear and the new row where data is inserted gets updated by unhiding the non blank rows.

But there's on thing that isnt solved with this method. Suppose I filter a date range where less items are sold. the pivot table 01 shows less rows. When this is done blank rows within the two pivot tables does not get hidden.

How might this be prevented?

[HansV]

A pivot table has a TableRange2 property that represents the entire range used by the pivot table (including the filter fields). You could use this to determine the number of rows you can hide:

Dim pvt As PivotTable
Dim rng As Range
Dim lngRows As Long
Set pvt = ActiveSheet.PivotTables("PivotTable1")
Set rng = pvt.TableRange2
lngRows = rng.Rows.Count

You can now calculate which rows you can hide.

[adam]

Thanks for the reply & and the code you have provided. To unhide the non-blank rows between the pivot table 01 & Pivot table 02 I'm using the following code.

Code: Select all

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
  Me.PivotTables("PivotTable1").RefreshTable
  Me.PivotTables("PivotTable2").RefreshTable
    Dim rw As Long
    For rw = 5 To 53
        Range("A" & rw).EntireRow.Hidden = (Range("A" & rw) = "")
    Next rw
    Application.ScreenUpdating = True
End Sub

In origin this is a Standard module code which I have changed into a worksheet event code by changing the line as Private Sub Worksheet_Activate()
so that I do not have to click the macro button each time I update the pivot table 01.

I have incorporated your code to the above code as follows.

Code: Select all

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False

Dim pvt As PivotTable
Dim rng As Range
Dim lngRows As Long
Set pvt = ActiveSheet.PivotTables("PivotTable1")
Set rng = pvt.TableRange2
lngRows = rng.Rows.Count
  Me.PivotTables("PivotTable1").RefreshTable
  Me.PivotTables("PivotTable2").RefreshTable
    Dim rw As Long
    For rw = 5 To 53
        Range("A" & rw).EntireRow.Hidden = (Range("A" & rw) = "")
    Next rw
    Application.ScreenUpdating = True
End Sub

But I'm unable to hide the empty rows after I filter a date range where less items are sold. I'm sorry If I have misunderstood your code or explanation.

On the other hand I would be happy if you could let me know what I have done wrong in here.

Thanks in advance.

[HansV]

The code that I posted doesn't do anything, it was just to give you an idea. But I don't understand which empty rows you want to hide. Could you try to explain more clearly?

[adam]

In the workbook attached I have hidden the rows 28:53 and place the pivot table 02 on row 54. As pivot table 01 gets updated by inserting rows the blank rows get hidden and the non blank rows get unhidden with data.

But as said before I'm unable to hide the empty rows after I filter a date range where fewer items are sold. Say for example I write 12/1/2009 in H1 & 12/02/2009 the rows in the Pivot table get reduced but do not hide the empty rows. Leaving blank rows between the Pivot table 01 & Pivot tables 02 and also pivot table 03

Basically my intention is to create a statistics report by the data taken from the sheet “Summary” & “Database”.

For this purpose I have created three pivot tables based upon data taken from the above mentioned two sheets.

Pivot table 01 is designed to show “statistics of sales for a given period of time”, most probably a period of 31 days (for this reason I have labeled all the column headers of the three Pivot tables with the date field.

Pivot table 02 is designed to show “statistics of total number of customers” including separate walk-in & walk-out customers who have received service within a given date range. This pivot table does not insert rows as it gets updated. Only the values in the corresponding cells get changed as the sheet “Summary” is updated.

Pivot table 03 is designed to show “statistics of sales for total number of customers” including separate walk-in & walk-out customers. This pivot table does not insert rows as it gets updated. Only the values in the corresponding cells get changed as the sheet “Database” is updated as this pivot table also gets updated with the information taken from the sheet “Database”.

As the date range is entered in the cell H1 & H2 Does excel has the ability to create a code that would filter the specified date range from the three pivot tables simultaneously.

Say for example I write 12/1/2010 in cell H1 & 1/3/2010 in cell H2 and press the macro button; the date range from the three pivot tables get filtered to show the data for the specified date range.

I wonder if excel has this capacity.

In spite of the odd I would be happy if I could get a positive answer for this question.

Thanks in advance.

[HansV]

Your originally had

For rw = 5 To 53

Why have you changed this to

For rw = 28 To 53

If you change it back, the code should work better for you.