Unhide Row as Pivot Table Gets Updated

[adam]

Hi Anyone,

I’ve place four pivot tables one below the other in my workbook.

I’ve hidden a couple of rows after the top pivot table to give room for it to extend as it gets occupied with more data.

But my code does not seem to un hide the hidden rows as the pivot table gets updated. As a result I could not view the total row.

Lets say for an example If I add new products & categories.

How may I overcome this?

Any help on this would be kindly appreciated.

I've attached the workbook for your reference.

[HansV]

Do you mean the Worksheet_Activate code for the Stats sheet?

[adam]

Yes Hans. I want the sheet "Stats'to unhide the rows as new categories & products get added to the sheet OrderData so that I could view the total row.

[HansV]

There is no code at all in Worksheet_Activate that would unhide rows. So "But my code does not seem to un hide..." is only to be expected. Excel doesn't execute code that doesn't exist.

[adam]

There is no code at all in Worksheet_Activate that would unhide rows.

Thanks for the reply Hans

The following does seem to work. I had missed that part in the attached.

Code: Select all

Private Sub Worksheet_Activate()
  Dim pvt As PivotTable
  Dim rw As Long
  Application.ScreenUpdating = False
  For Each pvt In Me.PivotTables
    pvt.RefreshTable
  Next pvt
  Application.Calculation = xlCalculationManual
    For rw = 21 To 1354
        If Not Range("A" & rw) = "" Then
            Range("A" & rw).EntireRow.Hidden = False
        End If
    Next rw
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

[adam]

How could I change this code so that it filters this pivot table with month and year each time the user changes the text in cells?

Any help on this would be kindly appreciated.

Note: the cell reference is the same as with the associated workbook with the code in here.

Thanks in advance.

[adam]

Never mind. I got it solved.

[adam]

I've implied the code from Post 21859 to the workbook attached. But when I select the month July The code does not hide other months except July. It works fine for other months. Say If I select January it works fine. But If I try to select any other month after selecting July it does not work.

What may be the reason for this. Is this because the month July is still ongoing?

I've attached the workbook for your reference.

[HansV]

I'd start by adding May to the list of months; it has miraculously disappeared.

[adam]

Thanks Hans. Somehow I missed May.

[adam]

How could I make the pivot table column headers with month names? Say for example I want the pivot table column “A” to be the product name after wards the remaining column headers to be January, February March and so on.

And under their names the total sum of products sold per that month.
For example total Number of apples for Jan. Total Number of Oranges fro Feb.

[HansV]

Select one of the dates in the top row of a pivot table.
Activate the Options tab in the PivotTable Tools group of the ribbon.
Click Group Selection.
Select Months and Years.
Click OK.

[adam]

Thanks for the help Hans. How could a pivot chart showing the data format as mentioned in Post 22036 be created from the pivot tables in Post 21869?

[HansV]

Apply the steps described in my previous reply to each of the pivot tables.

[adam]

Thanks for the help Hans.

[adam]

I’m trying to create pivot tables which would show statistical data by,
1. Day to day Sales
2. Sales by Month
3. Sales by Year.

For this purpose I’m trying create created three sheets of pivot tables with different categories.

But doing so I’m getting the debug message whenever I open these sheets pointing out the line
pvt.RefreshTable

Furthermore, if I change the format of the pivot tables in sheet “Statistics by Months” to the format as described in Post 22037 all the pivot tables’ format does change.

How may I overcome this?

Is this due to the pivot tables in all three sheets having the same source range?

[HansV]

It's not because the pivot tables have the same source range, but because they have the same pivot cache. In earlier versions of Excel, you could specify that a new pivot table based on the same source range as an existing one would use a new pivot cache, but in Excel 2007 this option was removed. Fortunately, fellow Lounger bradjedis found an easy workaround: create different defined names for the same source range, and use a different name for each pivot table.

You have used the name SourceRange as source range for the pivot tables. Create copies of this name, i.e. new names that refer to the same range but with a different name, e.g. SourceRangeMonth and SourceRangeYear, and use SourceRangeMonth as source range for the pivot table that groups by month, etc.

[adam]

Thanks for the reply Hans. I've solved it. And made pivot charts from the existing pivot tables. I have placed the four pivot charts that i have created in another sheet.

My question of concern is that would the following code referesh the pivot charts as the pivot tables get updated in another sheet with the data from the related sheets?

Code: Select all

Private Sub Worksheet_Activate()
  Dim pvt As PivotTable
  Dim rw As Long
  Application.ScreenUpdating = False
  For Each pvt In Me.PivotTables
    pvt.RefreshTable
  Next pvt
Application.ScreenUpdating = True
End Sub

[HansV]

A pivot chart should be updated automatically when you activate the sheet containing the related pivot table.
The pivot chart will not automatically be updated when you activate the sheet containing the pivot chart itself, unless you write code for it in that sheet's Worksheet_Activate event.

[adam]

Thanks for the reply. The pivot chart does get updated now.