VBA - Pivot Table Refresh

[Christian]

Hi all,

I am using Exel 97 and I am trying to put a macro together that
refreshes a pivot table after new data is inputted. The data and the
pivot table is in worksheet 1. The pivot table can change size depending
on the number of cells with data in it. I have set up a button (or
should I say Exel automatically set up a button) called
"CommandButton1". This links to the following VBA:

Private Sub CommandButton1_Click()
ActiveSheet.PivotTables("PivotTable2").RefreshTable
End Sub

When I press the button I receive the following message:

Run time error '1004':

RefreshTable method of PivotTable class failed.

I have looked up some older threads and it looks like I am inputting the
correct coding but obviously I am not.

Someone's assistance will be greatly appreciated.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Hardy]

This is code I have in recently designed sheet to refresh pivot table;

Worksheets("PivotTables").Activate
Set pvtTable
Worksheets("PivotTables").Range("PivHist20AvStdDev").PivotTable
pvtTable.RefreshTable

Probably do without the first line and the name ("PivHist20AvStdDev) i
dynamic named range that automatically sets to area of pivot table.
Set a range covering the table manually first. If it works and yo
want help setting up dynamic named range, let me know

 

[Debra Dalgleish]

Set the command button's TakeFocusOnClick to False, or add the following
line at the beginning of the code:

ActiveCell.Select

 

[Christian Papalia]

Thanks for both of your replies.

I have not got the spreadsheet that I am working on with me at the
moment but I tried it on a 'test' spreadsheet now and found that setting
the "TakeFocusOnClick" works.

So what does the "TakeFocusOnClick" do? And where can I learn more about
the other properties? I would have never found that one without your
help.

Regards,

Christian

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Peterson]

It's a bug in xl97 that was fixed in xl2k.

You can find lots of properties by looking at the object browser (F2) in the VBE
and by looking at VBA's help.

And a book probably would help. You can find a whole list of books at Debra
Dalgleish's site:
http://www.contextures.com/xlbooks.html

Maybe you could print it and visit your local bookstore to see if you can find
one you like.

The Activecell.activate kind of move the attention back to the worksheet itself.

In xl2k, your code will work if the .takefocusonclick is either true or false.