Automatically update pivot table when switching to sheet that the pivot table is in

What would the code be to automatically refresh a pivot table when you
open the sheet that the pivot table is in?  Today, I changed data in
the sheet that the pivot table draws from and forgot to refresh the
pivot table.  Fortunately, I caught my error in time and refreshed the
table before I gave it to the boss. I would like the pivot table to
refresh whenever I switch to the sheet that the pivot table is in.

Thanks
0
7/8/2009 3:25:55 PM
excel 39879 articles. 2 followers. Follow

4 Replies
720 Views

Similar Articles

[PageSpeed] 44

Try using the Worksheet_Activate event to update the PivotTable like:

Private Sub Worksheet_Activate()
PivotTables("PivotTable1").RefreshTable
End Sub

hth,
jay
<billshat44@yahoo.com> wrote in message 
news:a1c8c204-9e13-48b1-bc8c-051512c0b22c@b15g2000yqd.googlegroups.com...
> What would the code be to automatically refresh a pivot table when you
> open the sheet that the pivot table is in?  Today, I changed data in
> the sheet that the pivot table draws from and forgot to refresh the
> pivot table.  Fortunately, I caught my error in time and refreshed the
> table before I gave it to the boss. I would like the pivot table to
> refresh whenever I switch to the sheet that the pivot table is in.
>
> Thanks 

0
jte9999 (4)
7/8/2009 4:40:04 PM
Hi,

FYI - you can automatically refresh a pivot table when you open a file, 
regardless of the sheet by selecting the pivot table and choosing Pivot 
Table, Table Options, and checking Refresh on open.  This option applies on a 
pivot table by pivot table basis, it is not an Excel wide default.

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"billshat44@yahoo.com" wrote:

> What would the code be to automatically refresh a pivot table when you
> open the sheet that the pivot table is in?  Today, I changed data in
> the sheet that the pivot table draws from and forgot to refresh the
> pivot table.  Fortunately, I caught my error in time and refreshed the
> table before I gave it to the boss. I would like the pivot table to
> refresh whenever I switch to the sheet that the pivot table is in.
> 
> Thanks
> 
0
7/8/2009 7:44:01 PM
Thank you, this was exactly what I was looking for. When I opened up
the code sheet, there was already code as shown below:  Can you tell
me what this code is doing?  Do I need to leave it there or can I
delete it.

Thank you very much

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End


On Jul 8, 11:40=A0am, "Jay Emory" <jte9...@sbcglobal.net> wrote:
> Try using the Worksheet_Activate event to update the PivotTable like:
>
> Private Sub Worksheet_Activate()
> PivotTables("PivotTable1").RefreshTable
> End Sub
>
> hth,
> jay<billsha...@yahoo.com> wrote in message
>
> news:a1c8c204-9e13-48b1-bc8c-051512c0b22c@b15g2000yqd.googlegroups.com...
>
>
>
> > What would the code be to automatically refresh a pivot table when you
> > open the sheet that the pivot table is in? =A0Today, I changed data in
> > the sheet that the pivot table draws from and forgot to refresh the
> > pivot table. =A0Fortunately, I caught my error in time and refreshed th=
e
> > table before I gave it to the boss. I would like the pivot table to
> > refresh whenever I switch to the sheet that the pivot table is in.
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

0
7/8/2009 9:18:14 PM
It looks as though someone was trying to use the Worksheet_SelectionChange
event to run some procedure but never inserted any code.  So what you see
runs 'nothing' everytime you move the curser.  I would delete both lines.

hth,
jay
<billshat44@yahoo.com> wrote in message 
news:6ff6952e-5300-4b77-ab70-f62e8aea1ad5@m11g2000yqh.googlegroups.com...
Thank you, this was exactly what I was looking for. When I opened up
the code sheet, there was already code as shown below:  Can you tell
me what this code is doing?  Do I need to leave it there or can I
delete it.

Thank you very much

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End


On Jul 8, 11:40 am, "Jay Emory" <jte9...@sbcglobal.net> wrote:
> Try using the Worksheet_Activate event to update the PivotTable like:
>
> Private Sub Worksheet_Activate()
> PivotTables("PivotTable1").RefreshTable
> End Sub
>
> hth,
> jay<billsha...@yahoo.com> wrote in message
>
> news:a1c8c204-9e13-48b1-bc8c-051512c0b22c@b15g2000yqd.googlegroups.com...
>
>
>
> > What would the code be to automatically refresh a pivot table when you
> > open the sheet that the pivot table is in? Today, I changed data in
> > the sheet that the pivot table draws from and forgot to refresh the
> > pivot table. Fortunately, I caught my error in time and refreshed the
> > table before I gave it to the boss. I would like the pivot table to
> > refresh whenever I switch to the sheet that the pivot table is in.
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

0
jte9999 (4)
7/8/2009 10:04:54 PM
Reply:

Similar Artilces:

String Table (VC6 IDE)
I have strings in English language in the "String Table" of my project (myProject.rc). I'm loading them using: CString msg; msg.LoadString(150); Now, I need to internationalise my app. How can I do that? How can I add support for multiple languages? Which is the best way to do that? >I have strings in English language in the "String Table" of my project >(myProject.rc). I'm loading them using: > >CString msg; >msg.LoadString(150); > >Now, I need to internationalise my app. How can I do that? How can I add >support for multiple lang...

Controlling printed records when report bound to multiple tables
I created a report that uses the control from a form to generate a report based on that record's primary key. This form also has a subform which has relationships tied to the primary key for record identification and is linked to the main table. When preview the report the data from the subform either does not show up in the preview when using the filter [control]=[form]![control].[value] or makes multiple copies of the report equal to the number of entries in the subform's table. Is there any way around this? I have tried queries but have not found a way to use a f...

2007
Project desktop expert, new to Project Server 2007 and working with a sandbox implementation currently. When I use the PWA Build Team...Replace to replace a generic resource on a project with an actual named resource after first publishing a plan, my updates don't appear to be reflected in Project Server. If I check out and open the Project Plan in MSP Pro, however, the updates were made, and then if I publish the schedule, the updates appear in server. I thought when you made resource updates using PWA that they should be reflected immediately (or as soon as the request...

pivot table %
I have 2 columns in a pivot table - decription and amount. I need to calc a % of each value of the total. I don't know how to do that. ...

Enable Canadian Tax Detail option should update cost on receipt la
Would like the Enable Canadian Tax Detail option in the Company Setup Options window to work like how Landed Costs work. For example, if I select to post my tax detail to the Inventory account, I would like it to not only update my Inventory account in the General Ledger, but also to update the cost on the Receipt Layer in Inventory so when the Item is sold, it will sell at the Item Unit Cost plus the tax amount. The voucher created in Payables Management would just include the Item Cost without the tax amount. ---------------- This post is a suggestion for Microsoft, and Microsoft re...

Transferring Field from Existing Table/limitations and change of d
Thank you in advance for your help! I have two Excel spreadsheets that I successfully imported into Access 2003 and created tables for. I need to add the field from one table to the other, but there is not a direct match in the relationship. The large table uses the Employee ID as the primary key. The smaller table contains one field that lists a subset of these Employee ID numbers (a selection of certain employees). I need to transfer this field to the larger table, but I do not know how to tell Access to match up the corresponding numbers (i.e., the large table lists all employees, bu...

Query cross two table
Hi, I have two tables, tbAdmission and tbCode. In my tbAdmission, I have Code1, Code2 and Code3. In my tbCode, I have Code, Description1 and Description2. In my Form, frmAdmission, I have txtCode1, txtCode2 and txtCode3 that are all bounded to tbAdmission. And txtDescription1Code1, txtDescription2Code1, txtDescription1Code2, txtDescription2Code2, txtDescription1Code3 and txtDescription2Code3 that are unbounded and only for displaying the descriptions. txtCode1, txtCode2 and txtCode3 all refer to Code in tbCode to retrieve Description1 and Description2 for displaying in the unbou...

no change pivot item.
i want to can not change pivotitems in the pivotfields. namely pivotitems restircted. How can I do in VBA? *** Sent via Developersdex http://www.developersdex.com *** ...

Conversion Errors Table
Hello, I'm new to working with Access, I just converted an Access 97 databas into Access 2002. It tells me there were errors, and to look at th Conversion Errors Table. But nowhere in the message or in the MS Hel is there anything telling me where to find this table. Can someon help? Thanks Patric -- psha ----------------------------------------------------------------------- pshaw's Profile: http://www.officehelp.in/member.php?userid=493 View this thread: http://www.officehelp.in/showthread.php?t=125029 Posted from - http://www.officehelp.i I'd expect to find it in the new...

Move a particular named sheet to the end.
Using macros, how can I move a sheet called TOTAL to become the very last sheet within a workbook? Your assistance will be appreciated. -- Thank U and Regards Ann Try this: Worksheets("Total").Move after:=Worksheets(Worksheets.Count) "Ann" wrote: > Using macros, how can I move a sheet called TOTAL to become the very last > sheet within a workbook? > > Your assistance will be appreciated. > > -- > Thank U and Regards > > Ann > Barb, Works a treat, thank you for your assiatance. -- Thank U and Regards Ann "Barb Reinhar...

Estimates Invoices from a sheet
I have created a work book that I use to do Landscaping estimates. Th system combines sheets containing client info, material cost info labour info, sight measurements and pricing info to generate a estimate. The quote worksheet contains all of the potential component of a job wheather they are used in a given job or not. What I am no trying to do is produce a client ready estimate that picks up only th row information from the quote work sheet, that is active (contain values). In other words I don't want to preformat a estiamte form tha contains all potential quote items regardless of th...

if value not found in table ?
Hello all I need to display a msgbox if a value is not found in a table. Something like: If value not_in table.field then msgbix end if I know that code wont work is just an ilustration of what i am looking for Thanks in advance Use DLookup() to see if the value is in the table. If it's not found, the result will be Null. So, use IsNull() to test the result. Here's how to get your Dlookup() expression working: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access us...

automatically numbering rows
I wish to have rows automatically numbered in column "A" according to the content in column "B". If column "B" has text or numbers in a cell I do not want to number that row in column "A". If column "B" is blank in a cell I want to number that row in column "A". I found the following formula that works the opposite of what I want "=IF(B1<>"",COUNTA($B$1:B1)&".","")" . What would the formula be if I wanted to numerically count the blank cells in "B" and skip the conte...

Sorting Data Automatically
I am importing text into a new worksheet and would like to run a macro that can perform labour a labour intensive sort/deletion. Column A contains a mixture of text strings as follows: QR4567 QR4567/QT1223 QR4290 QR4290/QT1224 What I would like to do is determine how many QR's have associated QT's and how many QR's are remaining that dont have associated QT's. Any assistance would be greatly appreciated. Alan Bartley Sydney Australia Hi Alan not sure how the sorting comes into this - sounds more like needing a formula to count when a cell contains both QR & QT as op...

Displaying Sheet Tabs Names in Cells
Can anyone tell me if there is a way for me to display the Name of a Sheet Tab in a cell. Take for example I have 4 worksheets, labelled Shawn, Kevin, Mary, & Data. In data, I want it to show the name of the worksheets. So in Cell A4 I would like it to say Shawn, then in B4 I can put Shawn's data in. Is there a function or a formula that allows me to do this. Thanks for any help. Hi Maybe this UDF will do for you. ------ Public Function TabI(TabIndex As Integer) As String Application.Volatile TabI = Sheets(TabIndex).Name End Function ------ Insert the function in...

Populating work sheet combox with another work sheet values
Hi All, I have a combox in my sheet(1) which i want to populate with values present in sheet(2). I can populate combox in sheet(1) by setting the "ListFillrange"property to the required ranges in the sheet(1) (assume in coulmn A i have values from A1: A5) But i am unable to populate same combox with sheet(2) values(assume in sheet(2) C column i have set of values from C1: C10). I want to populate without using any macros. Can anyone Please help me to solve this problem Thanks in advance -- sjayar ------------------------------------------------------------------------ sj...

Linked Table Manager Doesn't Work in Access 2003
I recently upgraded to Access 2003 and have found that the menu option: Tools/Database Utilities/Linked Table Manager no longer works correctly for re-linking my front end database to the backend. No tables show in the table list. If I click “Select All” and then fill in the path to the backend database I get the following error message: Method ‘List’ of object ‘IfieldListWnd’ failed. As a work around I have to delete all the table links from the front-end and then File/Get External Data/Link Tables. Is anybody else having this problem? Thanks in advance for your help. "...

Pivot Text
I know that excel is probably not the place to be doing this but..... I am wanting to create a matrix of information that is currently all in columns text. I have tried to use pivot tables but obviously whenever I drop text into the values area it is ecpecting a number. What can I use as an alternative Excel 2007 PivotTable With Text (and tricks) http://www.mediafire.com/file/yt1zy2nwgm0/12_11_09.xlsm http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_11_09.xlsm ...

Protecting sheets & workbooks
Good morning ... Just two short questions ... a. What is the exact scope of protecting a WORKBOOK and not a SHEET ... what does a workbook prevent you from doing once it is protected b. When protecting a sheet or workbook, the user is still somehow allowed to right click on the sheet name and view and edit the code assigned to that workbook.... Is there some way this can be prevented ...? Thanks in advance, Brian a. Take a look at "workbook protection" in excel's help. There are a couple of settings that you can enable that allow the user to do fewer things. b. In...

Cond. Formatting depending of value in another sheet
Hi all, I've tried to do conditional formatting whether the cellvalue i another sheet is greater or smaller than the cellvalue I have in th active cell. I've tried to do "formula is" IF(B34<Sheet1!B34)and format, but it won work. Any help is apprecciated!! //Thoma -- Message posted from http://www.ExcelForum.com Hi Try: =B34<Sheet1!B34 -- Andy. "Jonsson >" <<Jonsson.1635tc@excelforum-nospam.com> wrote in message news:Jonsson.1635tc@excelforum-nospam.com... > Hi all, > > I've tried to do conditional formatting whether the ...

6/8/2010 Microsoft Office Security Updates failing to install.
All 6 fail to install. Both of the necessary Service Packs are installed on my system. Now what? Just delete them? Kirk MI wrote: > All 6 fail to install. > > Both of the necessary Service Packs are installed on my system. > > Now what? Just delete them? Lack of given information... "Both of the necessary Service Packs" <-- what necessary service packs for what prodcut. I know your subject says "Microsoft Office", but... yeah - there is not just one version of Microsoft Office and you might not even be speaking of Microsoft O...

Warning off by deleting a sheet
Hi Is there away to set warning off when i deleting a sheet in Code Sorry i have found the solution "alvin Kuiper" skrev: > Hi > Is there away to set warning off when i deleting a sheet in Code > ...

Deleting data from a table through a query
I have a database that is designed to update a list of credit union members from a master list so that vehicle insurance coverage can be tracked. I have been able to run an unmatched query to achieve a list of old members who have either paid off their vehicles or moved their loans to other locations. What I need to do now is delete these people from the main table. I have the cascade update and delete funtion in place to delete the vehicle information once the member is deleted, but I don't know how to take the information found in the unmatched query and delete those members fr...

centering text box in a table cell
Trying to do a project. I am attempting to center a text box in a row of a particular table. If I put it inline, I can kind of get it centered, but, it stays to the left side in the cell, doing about 32 of them, and they are all the same, tried adjusting, but must be doing something wrong. Select the text box, click on the dog icon on the picture toolbar, click none. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Bruce" <notreally@idontwantoo.com> w...

Issues with MSM 04 Updating Accts
i have MS M 04 and last week when i added a new bank to online services the updating function seems to have stoped working. When i update the accounts i get the summary sheet that says Act XXX has Y items to review, but when i open the account there are no items to review, and no items have been matched either (or rather it has not automaticly gone and matched trasactions). i have watied about a week, removed all online services and re added them and i still have this issue. MSM will update the accounts but now show me what has been downloaded, or let me match transcations. In microsoft.publ...