How do I find which Pivot Tables has an error in Excel 2010?
up vote
2
down vote
favorite
I'm receiving an error:
cannot open PivotTable source file ...XLSX SHEET...
while refreshing all data. I have many Pivot Tables, and I can't find that one with the error, while refreshing separately – there are no errors, no hidden sheets.
How can I know where the problem is?
microsoft-excel microsoft-excel-2010 pivot-table
edited Jan 19 '14 at 11:17
slhck
158k47437461
asked Jan 19 '14 at 9:52
Spon4ik
1615
add a comment |
up vote
2
down vote
favorite
I'm receiving an error:
cannot open PivotTable source file ...XLSX SHEET...
while refreshing all data. I have many Pivot Tables, and I can't find that one with the error, while refreshing separately – there are no errors, no hidden sheets.
How can I know where the problem is?
microsoft-excel microsoft-excel-2010 pivot-table
edited Jan 19 '14 at 11:17
slhck
158k47437461
asked Jan 19 '14 at 9:52
Spon4ik
1615
for now, deleted one by one sheets from workbook (copy), until refresh all works.
– Spon4ik
Jan 19 '14 at 14:01
Did the error message specify a sheet number? Have you tried saving as xlsb and then back to the original format? Sometimes that fixed some corruption issues for me.
– s_a
Apr 3 '14 at 10:42
1. no sheet number specified by error message. 2. didn't try, but I think wouldn't help, because there was a problem with one of pivot, found it by deleting sheets one by one.
– Spon4ik
Apr 6 '14 at 5:54
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I'm receiving an error:
cannot open PivotTable source file ...XLSX SHEET...
while refreshing all data. I have many Pivot Tables, and I can't find that one with the error, while refreshing separately – there are no errors, no hidden sheets.
How can I know where the problem is?
microsoft-excel microsoft-excel-2010 pivot-table
edited Jan 19 '14 at 11:17
slhck
158k47437461
asked Jan 19 '14 at 9:52
Spon4ik
1615
I'm receiving an error:
cannot open PivotTable source file ...XLSX SHEET...
while refreshing all data. I have many Pivot Tables, and I can't find that one with the error, while refreshing separately – there are no errors, no hidden sheets.
How can I know where the problem is?
microsoft-excel microsoft-excel-2010 pivot-table
microsoft-excel microsoft-excel-2010 pivot-table
edited Jan 19 '14 at 11:17
slhck
158k47437461
asked Jan 19 '14 at 9:52
Spon4ik
1615
edited Jan 19 '14 at 11:17
slhck
158k47437461
asked Jan 19 '14 at 9:52
Spon4ik
1615
edited Jan 19 '14 at 11:17
slhck
158k47437461
edited Jan 19 '14 at 11:17
slhck
158k47437461
edited Jan 19 '14 at 11:17
slhck
158k47437461
158k47437461
asked Jan 19 '14 at 9:52
Spon4ik
1615
asked Jan 19 '14 at 9:52
Spon4ik
1615
asked Jan 19 '14 at 9:52
Spon4ik
1615
1615
for now, deleted one by one sheets from workbook (copy), until refresh all works.
– Spon4ik
Jan 19 '14 at 14:01
Did the error message specify a sheet number? Have you tried saving as xlsb and then back to the original format? Sometimes that fixed some corruption issues for me.
– s_a
Apr 3 '14 at 10:42
1. no sheet number specified by error message. 2. didn't try, but I think wouldn't help, because there was a problem with one of pivot, found it by deleting sheets one by one.
– Spon4ik
Apr 6 '14 at 5:54
add a comment |
for now, deleted one by one sheets from workbook (copy), until refresh all works.
– Spon4ik
Jan 19 '14 at 14:01
Did the error message specify a sheet number? Have you tried saving as xlsb and then back to the original format? Sometimes that fixed some corruption issues for me.
– s_a
Apr 3 '14 at 10:42
1. no sheet number specified by error message. 2. didn't try, but I think wouldn't help, because there was a problem with one of pivot, found it by deleting sheets one by one.
– Spon4ik
Apr 6 '14 at 5:54
for now, deleted one by one sheets from workbook (copy), until refresh all works.
– Spon4ik
Jan 19 '14 at 14:01
for now, deleted one by one sheets from workbook (copy), until refresh all works.
– Spon4ik
Jan 19 '14 at 14:01
Did the error message specify a sheet number? Have you tried saving as xlsb and then back to the original format? Sometimes that fixed some corruption issues for me.
– s_a
Apr 3 '14 at 10:42
Did the error message specify a sheet number? Have you tried saving as xlsb and then back to the original format? Sometimes that fixed some corruption issues for me.
– s_a
Apr 3 '14 at 10:42
1. no sheet number specified by error message. 2. didn't try, but I think wouldn't help, because there was a problem with one of pivot, found it by deleting sheets one by one.
– Spon4ik
Apr 6 '14 at 5:54
1. no sheet number specified by error message. 2. didn't try, but I think wouldn't help, because there was a problem with one of pivot, found it by deleting sheets one by one.
– Spon4ik
Apr 6 '14 at 5:54
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
I had this issue in a complex spreadsheet. I deleted all tabs until I had a blank tab, and I still recievedc this message. I found that the underlying PowerPivot data model referenced a linked table that no longer existed. Deleting the linked table in the PowerPivot model resolved the problem.
answered Apr 11 '16 at 19:46
Aaron Bailey
1
add a comment |
up vote
0
down vote
Check VBA Code here. I modified it. If you run the code it will break if any error occurs.
Public Sub RefreshAllPivotTables()
Dim Sheet As Worksheet, Pivot As PivotTable
For Each Sheet In ThisWorkbook.Worksheets
For Each Pivot In Sheet.PivotTables
Debug.Print Sheet.name & "-->" & Pivot.name
Pivot.RefreshTable
Pivot.Update
Next
Next
End Sub
edited May 23 '17 at 12:41
Community♦
1
answered Sep 9 '16 at 11:49
Ram
170129
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f703847%2fhow-do-i-find-which-pivot-tables-has-an-error-in-excel-2010%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
I had this issue in a complex spreadsheet. I deleted all tabs until I had a blank tab, and I still recievedc this message. I found that the underlying PowerPivot data model referenced a linked table that no longer existed. Deleting the linked table in the PowerPivot model resolved the problem.
answered Apr 11 '16 at 19:46
Aaron Bailey
1
add a comment |
up vote
0
down vote
I had this issue in a complex spreadsheet. I deleted all tabs until I had a blank tab, and I still recievedc this message. I found that the underlying PowerPivot data model referenced a linked table that no longer existed. Deleting the linked table in the PowerPivot model resolved the problem.
answered Apr 11 '16 at 19:46
Aaron Bailey
1
add a comment |
up vote
0
down vote
up vote
0
down vote
I had this issue in a complex spreadsheet. I deleted all tabs until I had a blank tab, and I still recievedc this message. I found that the underlying PowerPivot data model referenced a linked table that no longer existed. Deleting the linked table in the PowerPivot model resolved the problem.
answered Apr 11 '16 at 19:46
Aaron Bailey
1
I had this issue in a complex spreadsheet. I deleted all tabs until I had a blank tab, and I still recievedc this message. I found that the underlying PowerPivot data model referenced a linked table that no longer existed. Deleting the linked table in the PowerPivot model resolved the problem.
answered Apr 11 '16 at 19:46
Aaron Bailey
1
answered Apr 11 '16 at 19:46
Aaron Bailey
1
answered Apr 11 '16 at 19:46
Aaron Bailey
1
answered Apr 11 '16 at 19:46
Aaron Bailey
1
1
add a comment |
add a comment |
up vote
0
down vote
Check VBA Code here. I modified it. If you run the code it will break if any error occurs.
Public Sub RefreshAllPivotTables()
Dim Sheet As Worksheet, Pivot As PivotTable
For Each Sheet In ThisWorkbook.Worksheets
For Each Pivot In Sheet.PivotTables
Debug.Print Sheet.name & "-->" & Pivot.name
Pivot.RefreshTable
Pivot.Update
Next
Next
End Sub
edited May 23 '17 at 12:41
Community♦
1
answered Sep 9 '16 at 11:49
Ram
170129
add a comment |
up vote
0
down vote
Check VBA Code here. I modified it. If you run the code it will break if any error occurs.
Public Sub RefreshAllPivotTables()
Dim Sheet As Worksheet, Pivot As PivotTable
For Each Sheet In ThisWorkbook.Worksheets
For Each Pivot In Sheet.PivotTables
Debug.Print Sheet.name & "-->" & Pivot.name
Pivot.RefreshTable
Pivot.Update
Next
Next
End Sub
edited May 23 '17 at 12:41
Community♦
1
answered Sep 9 '16 at 11:49
Ram
170129
add a comment |
up vote
0
down vote
up vote
0
down vote
Check VBA Code here. I modified it. If you run the code it will break if any error occurs.
Public Sub RefreshAllPivotTables()
Dim Sheet As Worksheet, Pivot As PivotTable
For Each Sheet In ThisWorkbook.Worksheets
For Each Pivot In Sheet.PivotTables
Debug.Print Sheet.name & "-->" & Pivot.name
Pivot.RefreshTable
Pivot.Update
Next
Next
End Sub
edited May 23 '17 at 12:41
Community♦
1
answered Sep 9 '16 at 11:49
Ram
170129
Check VBA Code here. I modified it. If you run the code it will break if any error occurs.
Public Sub RefreshAllPivotTables()
Dim Sheet As Worksheet, Pivot As PivotTable
For Each Sheet In ThisWorkbook.Worksheets
For Each Pivot In Sheet.PivotTables
Debug.Print Sheet.name & "-->" & Pivot.name
Pivot.RefreshTable
Pivot.Update
Next
Next
End Sub
edited May 23 '17 at 12:41
Community♦
1
answered Sep 9 '16 at 11:49
Ram
170129
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
1
answered Sep 9 '16 at 11:49
Ram
170129
answered Sep 9 '16 at 11:49
Ram
170129
answered Sep 9 '16 at 11:49
Ram
170129
170129
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f703847%2fhow-do-i-find-which-pivot-tables-has-an-error-in-excel-2010%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
for now, deleted one by one sheets from workbook (copy), until refresh all works.
– Spon4ik
Jan 19 '14 at 14:01
Did the error message specify a sheet number? Have you tried saving as xlsb and then back to the original format? Sometimes that fixed some corruption issues for me.
– s_a
Apr 3 '14 at 10:42
1. no sheet number specified by error message. 2. didn't try, but I think wouldn't help, because there was a problem with one of pivot, found it by deleting sheets one by one.
– Spon4ik
Apr 6 '14 at 5:54