Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1725
  • Last Modified:

ThisWorkbook.RefreshAll

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
Jagwarman
Asked:
Jagwarman
  • 2
  • 2
1 Solution
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
JagwarmanAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
JagwarmanAuthor Commented:
Not sure if it was your answer or not. Closed Excel, re-booted PC input your sub, ran without error.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now