Pivot Table based on data in protected cells
 

Good afternoon --
I have a template workbook that I want to release to a lot of users. Much
of the template is protected, but I want to add an unprotected page that has
a pivot table, based on a large table on a protected page.

The source data table has one line that's unlocked (the most recent month's
data) but the rest of it is locked and protected.

I have a button-driven macro that unprotects the worksheet, moves the data
to make room for the current month's numbers, then unlocks the current
month's cells and finally protects the worksheet again. What commands should
I try to allow this data to be the source for a pivot table on an unprotected
page?

Thanks in advance.

Pivot Table based on data in protected cells
 

Hi,
Not sure I understand...
You want the entire range (old + new month) to be the new source of the Pivot?

The best way is to:

1- In the first place, create a Name Range for your data, say MyData, and
use MyData as the source for the pivot.

2- Now everytime you add new rows, reset MyData to the new range, eg:
ThisWorkbook.Names.Add Names:="MyData", RefersTo:=.....
Then refresh the pivot:
Sheet1.PivotTables(1).Refresh
That's it.



Now everytime you



--
Regards,
Sébastien
<http://www.ondemandanalysis.com
<http://www.ready-reports.com


"pdberger" wrote:

Good afternoon --
I have a template workbook that I want to release to a lot of users. Much
of the template is protected, but I want to add an unprotected page that has
a pivot table, based on a large table on a protected page.

The source data table has one line that's unlocked (the most recent month's
data) but the rest of it is locked and protected.

I have a button-driven macro that unprotects the worksheet, moves the data
to make room for the current month's numbers, then unlocks the current
month's cells and finally protects the worksheet again. What commands should
I try to allow this data to be the source for a pivot table on an unprotected
page?

Thanks in advance.