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.
Total the columns
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Total the columns
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Total the columns
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
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.
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
Moreover the code is not excluding hidden columns on count.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Total the columns
The code fails for the hidden rows. In the attached version, I have added a test to exclude the hidden rows from being processed.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Total the columns
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?
What may be the reason?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Total the columns
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Total the columns
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?
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?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Total the columns
Does the attached version work better? I have added a date in August in OrderDetails.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Total the columns
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?
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?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Total the columns
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.
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Total the columns
Hans. Thanks for the guidance, recommendation and help. I do really appreciate it.
Best Regards,
Adam
Adam