You can only have a single worksheet_change event under any worksheet.

I'm confused at what you're changing and where the pivottable is.

If the data and pivottable are on different sheets (say sheet1 for the data and
the pivottable is on sheet2), then you only need the second version. Delete the
first procedure.

If the data and pivottable are on the same sheet, then delete the second
procedure.

wrote:

i tried the code like this:

Private Sub WorkSheet_Change(ByVal Target As Range)
With Me
.Unprotect Password:="TopSecret"
.PivotTables(1).PivotCache.refresh
.Protect Password:="TopSecret"
End With
End Sub

Private Sub WorkSheet_Change(ByVal Target As Range)
Sheets("Sheet2").PivotTables(1).PivotCache.refresh
End Sub

but it shows me that error

compile error
Ambiguous name detected: Worksheet_Change

i don't know why it shows me that error, I don't have experience with codes,
i'm new in this kind of work, please tell me how can i fix it.

thanks and best regards

"Dave Peterson" wrote:

Just look in the earlier post to see the code I suggested.

wrote:

I'm confuse too, it's possible that you can show me the complete code, with
your code in my initial code?.

thanks

"Dave Peterson" wrote:

Doesn't the code that was posted before do what you want?

I'm confused.

wrote:

ok, then i need to have the data and the pivot in different sheets? i tried
but i could get the result, I'm not to familiar with codes, could you please
tell me how can I do that

"Dave Peterson" wrote:

I think the finest level that you can refresh is the individual pivottable.

wrote:

thanks Dave but i tried to use it but is not exaclty that I'm looking for or
maybe I'm doing something wrong.

Let me try to explain and be more specific, I have the data and the pivot in
the same sheet, the first code that i had was to auto refresh in the same
sheet but it takes some time to auto refresh, i think the reason to this is
because i'm using ("Sheet2") and take time to read the complete sheet, is
there an option to just auto refresh an specific range of cells that contains
the data? ( for example: data:C5:E150, pivot table: H5:N150).

after that I want to protect the complete sheet except the C5:E150, and when
somebody put some data the pivot auto refresh automatically even with the
protect sheet, please tell me if this is more clear.

thanks and best regards

" wrote:

I have this code and without the sheet protection works good, but when i
activate the sheet protection it shows me an error, the code is as follows:

Private Sub WorkSheet_Change(ByVal Target As Range)
Sheets("Sheet2").PivotTables(1).PivotCache.refresh
End Sub

is there an option to avoid this error?

regards

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson