Total the columns

[adam]

These are the lines you had suggested to be removed. Right?!

Dim n As Long
n = .Row

I guess, the code works fine in the absence of them.

[HansV]

Correct, they aren't used any more.

[adam]

Thanks for letting me know, Hans.

In the attached workbook the “Grand Total” column from all the three pivot tables has been removed.

It is replaced with the column “Total”.

When the code is run it fills the “Total” with row totals. But the code does so; for the first pivot table. and shows the debug message highlighting the line

Code: Select all

If Application.WorksheetFunction.Count(Range(Cells(r, 2), Cells(r, t - 1)).SpecialCells(xlCellTypeVisible)) > 0 Then

Why isn’t it doing for the rest? Is it because the rows in between the first pivot and the second pivot are hidden?

Moreover the code is not excluding hidden columns on count.

[HansV]

The code fails for the hidden rows. In the attached version, I have added a test to exclude the hidden rows from being processed.

Total Columns.xlsm

[adam]

Thanks for the help. Your version of the workbook now totals all the visible rows in the "Total" column. But the code still counts the hidden columns, although it is intended not to do so.

What may be the reason?

[HansV]

It does, but you have to click the Insert Total button to update the totals.
If you want the totals to be updated automatically when you change the filter, you must call InsertTotal from the Worksheet_Change event procedure, and also from the ShowAllColumns macro: see the attached version.

Total Columns.xlsm

[adam]

Once again thanks for the excellent piece of work. But there seems to be a new issue now.

Suppose if I add a row to the sheet “Orders” with the date 5/8/2010 and a row to the sheet “OrderDetails” with the same date and open the sheet “Stats” the pivot tables does not get added with the new column.

Why is this?

[HansV]

Does the attached version work better? I have added a date in August in OrderDetails.

Total Columns.xlsm

[adam]

Yes. It works better than the others.

Does this modification mean, that I have to increase the hidden columns as more data gets added, so that whenever a new date is added the columns get visible by adding column data?

[HansV]

I moved the totals column to column AA, so that columns A:Z can be used for the pivot table. The unused columns get hidden automatically, just like the unused rows between the pivot tables get hidden.
If you expect to need more than 26 columns for the pivot tables, simply move the totals column even further to the right, and adjust the line

For cl = 2 To 26

accordingly (26 corresponds to column Z). But take care - you're using columns AK and AL so if you need to expand further, you'd need to move those too and adjust the code to match.

[adam]

Hans. Thanks for the guidance, recommendation and help. I do really appreciate it.