VBA - Pivot Table Refresh

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 ***
0
4/15/2004 9:06:49 AM
excel 39879 articles. 2 followers. Follow

2 Replies
1126 Views

Similar Articles

[PageSpeed] 9

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

--
Message posted from http://www.ExcelForum.com

0
4/15/2004 10:37:26 AM
Set the command button's TakeFocusOnClick to False, or add the following 
line at the beginning of the code:

       ActiveCell.Select

Christian wrote:
> 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 ***


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
4/15/2004 11:28:40 AM
Reply:

Similar Artilces:

Remove blank entries from a list box either using VBA or basic formula
Hi. I've got a spead sheet that asks the user to enter details of worker onto one sheet. ie. Surname.............First Name............Age.........etc.etc. This data is then used in a few pivot table reports. One pivot table simply lists the workers full names for entry int another sheer. The cells asking for the name are have a validation setting to listbox. The only problem I have is that the list box range is predetermined but the list may be from 1 names to 150 names. When entering the name into the new sheet using the dropdown box, i shows all blanks as well as the names ente...

How does excel do Top 10 in pivot
Hello All, Can any one tell me what logic does excel use to get top 10 or top 5 in the pivot. I am asking this because when you have a time series data you should ideally have Top 3 for each month or year but it just gives a list of products , now I wanted to know what basis, is it by the latest month or the first month. e.g Cat Jan08 Feb08 mar08 A 34 56 78 b 56 45 67 c 12 78 94 If I were to...

How to protect the query use for a Pivot that uses 'Get External D
I'm geting external data for a Pivot Table using the 'Get External Data' option, this works fine and retrieves the information from a SQL 2000 database view. In order to don't retrieve "all" the records, I use a filter (ie. where Company equal to Microsoft), that way the User only sees that amount of data that he is supposed to see. The problem I have is that a power User that knows excel will be able to edit/remove that filter and see "all" data. Of course I can create different database views (ie. one for each filter) and apply proper access right...

forgot my vba project passowrd
guys I forgot my vba project password for one of my old file. how do i remove this password The below link should help. http://www.mcgimpsey.com/excel/removepwords.html -- Jacob "linto" wrote: > guys I forgot my vba project password for one of my old file. > how do i remove this password thanks the site talks about some hex editor for vba project passwords. I dont know whats this and how to do it. any help? "Jacob Skaria" wrote: > The below link should help. > > http://www.mcgimpsey.com/excel/removepwords.html > > --...

Update combo from table
Hi, I am working on a database I did not create, always fun. I have a form with a combo box on it for client name. There is a button to add a new client. When you click on it, it brings up the table and you enter in all the client information. This particular form only needs the client name. Is there a way, when you close the table, to have the client name combo box update so the user doesn't have to reload the whole form? What happens is the user will fill in a lot of information on this form, realize they need to add a client, do that, then have to re-type everything because...

Removing artifacts from a spreadsheet with VBA
I copy excerpts of HTML tables into spreadsheets and get "artifacts" some of which respond to clicking to select and then using DELETE or CUT. When the cell/row they're in is deleted they migrate to the adjcent row/cell. How can I identify and remove these with VBA? I've tried to "Record Macro" to see what the genie does, but all he says is: "Selection.Delete" and that doesn't tell me what was selected! So, it's useless to include it in my code. I want to get rid of them because the really drag in a lot of something that slows d...

MS Access Report Graph Table
I cannot get my report to retain the currency format when previewed. It looks fine in design view, but does not retain it. I have changed all of the underlying queries and tables to include currency formatting. Any help would be appreciated Bev, Check the Format property of the field on the Report. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Bev" <Bev@discussions.microsoft.com> wrote in message news:0D5DBFA2-2CB4-4C2E-AE6A-C5DAE41EC1F2@...

Change font for each text box using VBA
The front page of a report has fields that a user can change according to their needs. Is there a way for the user to specify the font type and size for each control individually? Possibly for just each section (a couple of headers and the detail sections)? I've tried: With Me.GroupHeader0 Me.FontName = Me.TitleLineFontName Me.FontSize = Me.TitleLineFontSize End With TitleLineFontName and TitleLineFontSize are fields in a table in which a person specifies the font type and size. Am I on the right track going that direction? I'm using Access 2007. ...

Table Import error #2
Hi, I get this error when I try to open Table Import - 'Open Operation on Import_Detail failed because path does not exist.' . One suggestion I got is to check the user permissions on the folder where the import tables are created, but how do I find what folder that is? We use GP 8.0. Thanks, Peter ...

How do I put a "green bar look" in my Access 2007 tables?
How do I put a "green bar look" in my Access 2007 tables? "Cajunguy55" <Cajunguy55@discussions.microsoft.com> wrote > How do I put a "green bar look" in my Access 2007 tables? Short answer: you don't. Long answer: you shouldn't be working directly in the tables unless it is a personal-use, end-user database; you should be using forms and reports that display the information. In reports, the "green bar" effect can be accomplished with code in the report itself. Larry Linson Microsoft Office Access MVP ...

Pivot Table Help #5
Good Morning I have a pivot table set up and all is ok i have set up a macro to copy the data in the pivot table and to paste it sumwhere else and to sort it all is good, but when i save the spreadsheet and close it down and reopen it and run the macro the pivot table shrinks down to 2 rows and 2 colums how do i get my data back on show Can anyone help Thanks in Advance Steve can u pls send your file to me: mohdraf@hotmail.com. i hope, i can help you "Debby Bunce" wrote: > Good Morning > > > I have a pivot table set up and all is ok > i have set u...

inserting multi-page excel table into Word
I'd like to be able to insert a large (i.e. multi-page) range of data into Word 2000 in one operation and have it paginate properly. At the moment, I have to cut and paste one page worth of data at a time onto each Word page and fiddle with the sizing. Is there an easier way to do this? ...

Find the value of a point with VBA
Hi all, Pardon my english ! I'm french. I need to find the values of points of a serie (in charts of course) with VBA. I can't refer to the value of the source cells because of the very bad organization of the source sheets. The only way I found is to add datalabels, pick the datalabels values, convert them to numeric values. Is there a shorter method to do that ? (XL2K) I did not find the answer on the french speaking excel newsgroup though it's very active and frendly. Thanks -- Fran´┐Żois L Hi, This will get the values from series 1. Note that linked cells with erro...

Advice: XML file as database or table
Hello, If I choose to use a database then I need to use multiple tables. With a XML file I can select "my objects" as once. I think that there will be around 10.000 records (objects). I want to select my objects on different (selection criteria) ways (on attributes). And I want to add, update and delete my objects. Can somebody tell me if it is a good idea to use a XML file as database or table? Can I read parts of a XML file (for paging purpose)? Or do I need to select everything at once? Thanks! ...

Updating TABLE with Query ( Update?)
I have a table that i would like to update with results of a seperate query I would like to update table1.Title with query.title table1.quantity with query.quantity where table1.item = query.item So I had to use maketble query and then update from the temp file to get it to work. Is that a work around or the way it needs to be done ? "sfleck" wrote: > I have a table that i would like to update with results of a seperate query > > I would like to update > > table1.Title with query.title > table1.quantity with query.quantity > > where table1.it...

2003: Can't start VBA Editor
Hi - I'm using Outlook 2003. I started using the Visual Basic Editor yesterday. Today I can't even open it or get the macro dialog to display. There is no error, but neither the editor nor dialog shows up. I tried using Setup to repair my Office installation, but that didn't help. Johnny ...

Redefine Table Array
I already have set up a Vlookup but i want to add more items to this how to i make the range include the newly added data I like to put my table on a dedicated worksheet. Then I can use the whole column without worrying. =vlookup(a1,sheet2!a:e,3,false) But you could use a dynamic range name that grows/extracts with your data. Take a look at Debra Dalgleish's site to read more about it: http://www.contextures.com/xlNames01.html#Dynamic Shaya M wrote: > > I already have set up a Vlookup but i want to add more items to this how to i > make the range include the newly added da...

About pivot tables
Hello, I am working on a pivot table that displays data from various dates from 1999 to 2005 and i would like to filter it to show only the data coresponding to dates up to march 2003. How do i do that? I cant go do it manually from the date column because the dates are over much to many and if I try to hide details for months jan to march , it seems that it hides the data for all years including 2004 and 2005 which i want calculated in the pivot table. any help guys? thank you in advance, bay. Right-click the Date field button Choose Group and Show Detail > Group In the End Data...

Why is the order of my data table opposite from graph?
I'm using a simple bar graph with a data table. The data table is in the opposite order of my graph. I've tried resorting the source data, I've tried reversing the categories on the axis. I keep getting the same result - the Y axis is in alpha order from top to bottom (which is what I want) but the data table is in reverse alpha order (alpha goes right to left) I would first check the formatting of the data to make sure it is formatted correctly. If there are no data format problems, can you provide an example of the data that you're trying to chart? "mozermodo&qu...

VBA
A window is appearing Project Explorer and cannot be removed using the "Remove Current Window from VBA" Option. When adding the Window again it appears ad WindowName1. How is an unwanted windpw removed from VBA. Thanks. -- Mick If the window is a VBA User Form it will need to be deleted from the VBA Project Explorer. If it is a GP window then it is possible that your VBA files have been corrupted. Whenever I get weird behaviour in VBA, I export all my customisations as packages, rename the *.VBA files and custom reports and forms dictionaries and then import everything back...

Table Size Limitations 09-08-05
Hello, I am hoping you can help me answer this probably easy question. I am trying to add some new columns to the contacts table in 1.2 and I believe I am running into a space limitation. I have added about 550 bytes of information to the table and then I started getting the error. There error that I am getting or the system log does not have any information in it as to what the error is. I am a little confused as to why I would be getting this error, I realize that there is a 8000 byte sql limit, but the stock contact table has around 3700 bytes and I have only added 550 bytes. Whe...

VBA Code Written Once For All My Forms
I have a couple procedures that I use for dozens of forms in teh same database. The code for this prodecure is identical. It's not in every form, but many. Can I reference such a procedure from any form I want, but write the procedure once? On Sat, 6 Mar 2010 17:21:01 -0800, VWP1 wrote: > I have a couple procedures that I use for dozens of forms in teh same > database. The code for this prodecure is identical. It's not in every form, > but many. Can I reference such a procedure from any form I want, but write > the procedure once? The usual way...

access db carry forward balance vs. table design change?
with respect to this post. http://www.eggheadcafe.com/software/aspnet/31309039/updating-several-records.aspx i am working on a database to use in a small town for home water usage tracking and billing. the db is structured with a customer info table and a monthly usage and charges table. there is a relationship setup for the home address field, one to many. kinda like this.... tblCustomerInfo:..... fName lName address phone tblMonthlyReadings:.... address readMonth(Date) meterReading paymentAmount amountBilled amountPastDue lateFee credit i think what i want to do is ca...

comparing componants of tables
I cannot easily compare data elements of comparative data. Here's what I mean, john smith observation 1 observation 2 weight 160 155 belt size 40 38 shoe size 12 11 bill jones observation 1 observation 2 weight 128 119 belt size 29 28 shoe size 8 9 mary Dee observation 1 observation 2 weight 120 129 belt size 25 26 shoe size 8 ...

Suppress Zeros in a Pivot Table
Can I suppress zeros in a Pivot Table? Many of the results are zero, how can I hide the rows without using a macro. thanks Simon Shaw You could add a column of formulas in a hidden part of the pivot table worksheet, then use it to filter the pivot table. For example, if your pivot table is in cells A3:J100, and has two row fields, enter the following formula in cell R2: =AND(A2="",B2<>"",SUM(C2:P2)=0) Copy the formula down to the last row to which the pivot table will potentially reach In cell R1, enter a heading, e.g. Filter Select cell R1, and choose D...