Free Microsoft Excel 2013 Quick Reference

Provide option to refresh pivot table before saving and closing workbook


Can someone please help me make a VBA Code.

I am looking for a VBA code that would automatically run when the save button on a spreadsheet is hit.

I would like the code to:

1. Have a Popup box come up saying - would you like to refresh your pivot tables.
If you hit no - then the file saves and closes.
If you hit yes - then:

a- it goes to the defined name "Table1" and refreshes the pivot table
b - it goes to the defined name "Table2" and refreshes the pivot table
c - saves the file
d - closes the file

Thanks so much.

Post your answer or comment

comments powered by Disqus
I saw macros on how to refresh pivot table when worksheet is selected.
But I am looking for reference code on how to refresh pivot tables on
entire workbook when I save the file.

I have a pivot table in spreadsheet A.

I have some formulas in another spreadsheet B.

The formulas look up data in the pivot table in A and use that data in B to return and answer.

Is there a way to refresh the pivot table in A from B without having to open A and refresh the table?

Hope that makes sense.


Question: Macro to save and close workbook

This is what I have come up with, I have found three issues with it.
1. One it closes all the workbooks I have open and only saves the one that the macro is in.
2. Two if I put this macro in more than one workbook I can’t have both open at the same time.
3. Three I got the missage to come up You have 5 minutes to save before Excel closes. But if no one clicks that message the workbook stays open.

Running Excel 2003 with Windows 7

Private Sub Workbook_Open()

Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True
TimeInMinutes = 600
If TimeInMinutes > 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
   Start = Timer
   Do While Timer < Start + TotalTimeInMinutes
   Finish = Timer
   TotalTime = Finish - Start
   Application.DisplayAlerts = False
   MsgBox "This file has been open for " & TotalTime / 60 & " minutes.  You have 5 minutes to save
before Excel closes."
End If

Start = Timer
   Do While Timer < Start + (5 * 60)
   Finish = Timer
   TotalTime = Finish - Start
   Application.DisplayAlerts = False
   MsgBox "Excel will now close."
   ActiveWorkbook.Close True
' closes the active workbook and saves any changes
End Sub

Hello OZ Grid mates,

I need help again. I now have a Pivot table on my worksheet that works great, the only problem I have is I would like it to refresh my data at the push of a button. I now have to click on a cell where the pivot table data is>right click the mouse>select refresh data and then I get the updated info. I know you can install a button to to this exact same thing but I don't quite know how to. I need step by step instructions. I tried OzGrid search, but did not find exactly my need.

This is what I did thus far I opened my work book>click on data>then macros>record new macro>I got a pop screen>I named the macro Refresh_Pivot_Table>store to this workbook>click OK> I see at the bottom left of my screen that the macro is still recording> I then highlighted the Column of cells where the pivot table data is and then clicked>tools>macro>stop recording.

This is the point where I'm lost. What is the next step to do? When I go back to the cell where I began to start the macro and right click I don't get the option to create a text box so that I can make a button and assign the macro I just recorded. Is this the correct method or did I miss something?

Thanks in advance...

I've created a daily input worksheet and it kicks out the results to a pivot
table. I want to create a button on that chart for the user to click on so
it can refresh data. Doing so just in case they don't have the pivot table
menu open, or forget to click in chart to bring up the refresh data button

I've created a sourc e data worksheet, that kicks out to a pivot table in
same workbook. I want to add button to the sheet where the user would just
have to select it, to refresh data. Just trying to make it easy for those
that may not have their pivot table bar up, or forget to click in chart to
bring up refresh button.

Any ideas


I have a spreadsheet that has multiple pivot tables on different tabs. This program is going to people that do not know how to manipulate pivot tables, therefore I want to protect each sheet, only allowing access to the page fields.

I have:
INPUT SHEET containing PivotTable1

BAKESHEET containing a PivotTable30 that is based on PivotTable1

ANALYSIS containing three pivot tables each bases on PivotTable1

I have a routine that copies the newly inputted information over to the database (seperate file). the user then needs to return and be able to access that information on the BAKESHEET pivot table, which will need refreshing before displaying the new page field.

I have tried to use the workbook.activate functino to refresh pivot table 1, but I get the message cannot refresh pivot tables based on this one if they reside on a protect sheet (or words to that effect) I can't base each of the pivot tables on the original data as the file size is huge.

any advice would be greatly appreciated




I have multiple pivot tables all on different worksheets. I also have a server queries from which the pivot tables draw from. I would Like to click one button and have both the server queries and pivot table refresh.

I have tried

This seems to be the most elegant way, but maybe it is instantaneously refreshing both the query and pivot table, that the
pivot table isnt updated with the new info from the query? I say this because it will refresh the server queries, but not the
pivot tables.

I have also used a seperate module to refresh the server queries, and then tried using

 dim w as worksheet, p as pivottable
for each w in thisworkbook.worksheets
  for each p in w.pivottables
all in the same sub, but the pivot tables refuse to refresh unless I go to the sheet which contains the unrefreshed pivot table, right click, and refresh data.

I am thinking possibly a module to call in my sub, inserted after my query refresh sub, will refresh the pivot tables which draw from the server query.

Anyone else have any ideas or insights into what I am doing wrong?

Thanks in advance

Hi here is my code that opens a excal file from a access form, what i like to do is when i open this file from a access form through the onclick button is for the pivot table to refresh also. Is this possible for this to happen, my data source is from the access database.

    On Error Goto Err_UK_Cost_Report_Click 
    Dim oApp As Object 
    Dim wrkFile As Object 'Workbooks
    Set oApp = CreateObject("Excel.Application") 
    Set wrkFile = oApp.Workbooks 
     ' Open a file.
    wrkFile.Open "B:Report.xls" 
    oApp.Visible = True 
     'Only XL 97 supports UserControl Property
     ' to refresh pivot table on opening
    MsgBox "You've closed the Report," & Chr$(13) & _ 
    "and return to the main screen." 
     ' Close the file.
     ' Quit Excel.
     ' Close the object references.
    Set wrkFile = Nothing 
    Set appXl = Nothing 
    On Error Resume Next 
    oApp.UserControl = True 
    Exit Sub 
    MsgBox Err.Description 
    Resume Exit_UK_Cost_Report_Click 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hello all, I would like some VBA coding to refresh pivot tables in worksheets named "previous", "teams" and "h2h" when a cell value is changed in the worksheet "previous" column D:D

There are anywhere from 1 to 5 pivot tbales on the sheets.

Please could someone advise the simplest way to do this?

Hi Every Body,
I need code to refresh pivot table data when i move between sheets

I have a workbook that many users share. Sometimes for reasons unknown the workbook is left open. Sometimes typed data is lost from people not saving before closing the book. Any way to set a timmer that save and closes the book say after 10 minutes of inactivity? Any help id appreciated.

I have a few workbooks on our server that need to be opened, links updated,
saved and then closed again. It has to be done in a specific sequence and
each workbook has the same writeres password. I'm thinking along the lines of
having a master workbook open containing the macro. When I leave work in the
evening I can start the Macro and have all the workbooks updated by the next
morning. I have been testing this on one book only. It opens and updates but
it doesn't want to close it.
I don't know much about VBA and sort of been figuring things out as I go

This is what I've been trying:

Sub OpenUpdateClose()
' OpenUpdateClose Macro
' Opens all workbooks, Updates them then saves and closes them
' Keyboard Shortcut: Ctrl+Shift+O
Dim wbk As Workbook

Application.DisplayAlerts = False

Set wbk = Workbooks.Open(Filename:="filename1.xls", _
UpdateLinks:=True, _

With wbk
End With

Set wbk = Nothing

End Sub

Thanks in advance for the help

Hello Everyone,

I have a workbook called xfer that i open and copy new data to every hour. What I want to do is open the work book, run my macro to copy the fresh data to, and then have the xfer workbook save and close without asking me if I want to save the workbook.

Thanks for your time,


Hi all

I've got a spreadsheet that I need to update in order to be able to run reports later in the day. Now, because of changes, it takes forever to calculate all the changes and save.

Does anyone know of a way, either in macro or scheduled task form, to get the spreadsheet to open, update the links, save and close, but be able to be opened normally witha prompt to update links if anyone needs to make any "proper" changes to it?

All advice gratefully received.

I would like oi find a code that would automaticaly save and close a workbook 10 min after this workbook was opened.

can someone help me !!


I would like to open, (if possible Update), Save and Close automatically
several excel 2007 files.
Actually I do this manually file by file and I lose 1/2 days in each week.
I' m looking for an process that simplify this task.

Hugo Pinto

I want to auto-save and close a workbook after, say, 30 minutes of inactivity. Is this possible?

Geoff Culbertson
Petersfield, Hampshire, UK

I have a workbook that gets viewed/edited on different computers throughout our shop, and the situation often arises where someone forgets to close the workbook after they are done with it, which means if someone else at a different station tries to open it, they aren't able to do any editing.

I'm wondering if a way I could solve this problem would be by having the workbook automatically save and close after two minutes of inactivity. I don't know if Excel supports this kind of function, but that's why I'm asking.

Any ideas?

I noticed your informative response and would appreciate your response on a
little problem I have with the Excel worksheet all of a sudden. When I
select refresh pivot table after I made changes to the data worksheet, the
pivot table goes blank it only shows the first column with the dates. This
has worked thousands of time before but not today.

Please advise.

Best Regards,

"Dave Peterson" wrote:

> Even if you could, I'm not sure that's a good idea.
> I figure that MS made refreshing the pivottables a manual effort to keep the
> time between worksheet changes at a minimum.
> Imagine lots of pivottables and each one trying to recalculate each time you
> change a value in the pivottable range.
> But maybe you could refresh it when you select the worksheet that contains the
> pivottable (is it on a different worksheet???)
> rightclick on the worksheet tab (with the pivottable) and click on select code.
> Then paste this in:
> Option Explicit
> Private Sub Worksheet_Activate()
> Dim myPT As PivotTable
> For Each myPT In Me.PivotTables
> myPT.RefreshTable
> Next myPT
> End Sub
> If you have lots of pivottables in different sheets, you can get them all with a
> line like:
> ThisWorkbook.RefreshAll
> Tom Nichol wrote:
> >
> > I am using Excel 2000.
> > I have a pivot table that summarizes data from a database on a separate sheet.
> > When I delete rows from the database sheet, I must click the refresh button
> > on the Pivot Table before it will recognize those deletions.
> > Is there a way to have the pivot table automatically refresh the instant the
> > rows are deleted?
> --
> Dave Peterson

I am trying to hide a "Page" option on a pivot table. I need to hide disable the "(all)" option and create another one with an accurate calculation. I can handle making the other one but I cannot seem to find a way to hide the "(all)" option. If anyone can provide any help it would be greatly appreciated. I alredy tried making another "(all)" section but it just lists 2 all sections on the page view drop down on the pivot table. Thanks,



I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).

Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already you want to overwrite.." prompt.
Which defeat the automate process.

Any other solution to enable the automatic refresh on open the excel workbook?

Or Access can overwrite the exist file or save it as another file name with timestamp ?


I have a worksheet called "FindUnique" which has Pivot Table named: "PivotTable1" and Macro named "FineUnique".

I have another worksheet called "Summary".

Whenever I select the worksheet Summary, I would like to auto refresh the PivotTable1 and Macro. I need the code please.

I have saved datasource in my personal drive, and pivot table is saved in the
network drive, so everyone could drill down to their own department, I have
refreshed pivot table before I saved it. Then I worked on pivot table for
various analysis.

But the others could n't drill down their dept, they've been able to drill
down before, anyone has clue why the other people couldn't drill down their

No luck finding an answer? You could always try Google.