I have sheet "Sheet1" with a pivot table.I have defined a global
range name called "members". In pivot table range I used "members"

later I have copied this sheet. Its name became "sheet1(2)".
I have changed the data in the "members" range name. The range to
which the pivot table refers is of sheet1(2). The name "members"
became local name.

When I update the pivot table, both Sheet1 and Sheet1(2) pivot tables
show the same thing.

Later I went to sheet1 and updated the pivot table there, then
sheet1(2) pivot table also changed.

I want the pivot table to work only for that sheets data