Solved

ThisWorkbook.RefreshAll

Posted on 2013-01-03
4
1,464 Views
Last Modified: 2013-01-03
Does anybody know why ThisWorkbook.RefreshAll is not only refreshing all of the pivots in my workbook but also removing formula that I have in the underlying data.

i.e. formula in cell is =TODAY() which is displayed as 03/01/2013

after running macro with ThisWorkbook.RefreshAll cell changes to 27/12/2012

formula in next cell is =H2 which displays 11

after running macro with ThisWorkbook.RefreshAll cell changes to 4

thank you
0
Comment
Question by:Jagwarman
  • 2
  • 2
4 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 38739576
That doesn't make sense!

That method refreshes all external data ranges and PivotTable reports in the specified workbook.

Check if the values are not changing from another macro.

You just want to refresh the pivot tables?
0
 

Author Comment

by:Jagwarman
ID: 38739867
I have no links to other workbooks that I can see and no other macros in my spreadsheet.
This is the most bizzare thing I have ever seen
0
 
LVL 48

Accepted Solution

by:
jpaulino earned 500 total points
ID: 38739881
Can you upload the file?

If you just need to refresh the pivottables you can try this way:

Sub RefreshPivotTables()
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In Worksheets
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next
Next
End Sub

Open in new window

0
 

Author Closing Comment

by:Jagwarman
ID: 38740689
Not sure if it was your answer or not. Closed Excel, re-booted PC input your sub, ran without error.

Thanks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question