Solved

ThisWorkbook.RefreshAll

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
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 …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

738 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