Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ThisWorkbook.RefreshAll

Posted on 2013-01-03
4
Medium Priority
?
1,580 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

719 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