how to view or edit database in pivot table?

[learning_grexcel]

I have created a pivot table from a database. Now I wanted to delete and make some changes in the database and it should be automatically affect the pivot table instead of doing it all over again from the source. How it can be done?

 

[Akashwani]

Hi,

I don't write or understand VBA so I am unable to help you further with code.

If you record a macro whilst you refresh your Pivot Table you should be able to put this in the worksheet code of the sheet where your Database is.

I recorded this macro...

Sub RefreshPT()
    Sheets("Pivot Table").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

I then right clicked the worksheet where my Database is, clicked View Code then added this code....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
RefreshPT
End If
End Sub

I guess this would also work in the Worksheet code....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Sheets("Pivot Table").Select    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End If
End Sub

You will need to change the sheet name from Pivot Table to the name of the sheet where your Pivot Table is.
You will need to change the Pivot Table name from PivotTable1 to the name of your Pivot Table.

I hope that helps.

Ak

 

[learning_grexcel]

Thanks
I don't know exactly how to do this. I'm completely new to macro....I will be glad if you can explain to me step by step how to use this code in excel 2007

 

[Akashwani]

Hi,

Open your workbook.

Press alt F11
Click Insert, click Module
In the white box paste this code...

Sub RefreshPT()
    Sheets("Pivot Table").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

This assumes that the sheet with the Pivot Table on is called Pivot Table, if it isn't, change the above to your sheet name. If your Pivot Table is not called PivotTable1 change the above to the name of your Pivot Table.

You could add a button to your worksheet and assign the macro RefreshPT.
When you have made the changes to your database, you can click the button and your Pivot Table will refresh (hopefully)

Good luck.

Ak

 

[learning_grexcel]

Thanks again
I tried but I'm getting the following error :
"runtime error '1004';
Unable to get the PivotTables property of the worksheet class"

I think the problem is Pivottablename. I don't know what is my pivottable name. I just left it "pivottable1" of yours. how to check my pivot table name?

 

[learning_grexcel]

Finally, I found pivottable name and I done that you mentioned in post #4. Now what I have to do to view and edit my database.

 

[Akashwani]

Hi,

I don't understand what you now need to do.

Have you added a button to your database worksheet and assigned the macro to it?

Ak

 

[learning_grexcel]

Thanks Akashwani.......
I don't know which button u r talking about.
I pressed Alt + F11 and I put the code in "insert">> "module" >> saved it. I put my sheet name and pivot table name in the code also.
After that I went to my pivot table to see if I can see anything...but nothing...

 

[Akashwani]

Hi,

On your Database worksheet click Insert on the tab at the top.
Click Shapes, select a shape you want to use as a "button".
When you have created the "button", right click it, click Assign Macro.
Double click the macro RefreshPT, save your workbook.

When you make a change to your Database worksheet, click the button and hopefully the Pivot Table will refresh.

Good luck.

Ak

 

[learning_grexcel]

Thanks a lot. It works perfect.

Now I would like to try your second code and third code in post #2 though I don't know what they are meant to.

If I right click my database there is no option called "view code". How to get it?