Solved

ThisWorkbook.RefreshAll

Posted on 2013-01-03
4
1,533 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 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

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

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

628 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