Why does the dates filter does not work in a Pivot when recording a macro in Excel?
I have a sheet in Excel with a date column (dd/mm/yyyy)as follows:
A$Period Name
01/01/2018
01/02/2018
01/03/2018
When I look at the filter in the table I can see the dates as follows:
If I record a macro to filter this column I get the following code, which when I run it back it works correctly
ActiveSheet.Range("$A$1:$BP$5413").AutoFilter Field:=17, Operator:= _
xlFilterValues, Criteria2:=Array(1, "10/1/2018")
The problem I have it in a pivot on the same data set. The date filter in the pivot looks like:
When I record the macro the code lookslike:
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = "10/1/2018" ' I tried with all dates. Showing Oct. here
When I try to run it back I get an error message in the last like like: Run-time error '1004': Application-defined or object-defined error.
I tried to change the date as dd/mm/yyyy (like the source) and dd-mmm-yy (like in the pivot) but it does not make a difference.
Any ideas on how to make it work? Also, I don't understand why when you record it the date it changes to mm/dd/yyyy.
excel vba excel-vba pivot
asked Nov 12 '18 at 10:01
Selrac
93121846
add a comment |
I have a sheet in Excel with a date column (dd/mm/yyyy)as follows:
A$Period Name
01/01/2018
01/02/2018
01/03/2018
When I look at the filter in the table I can see the dates as follows:
If I record a macro to filter this column I get the following code, which when I run it back it works correctly
ActiveSheet.Range("$A$1:$BP$5413").AutoFilter Field:=17, Operator:= _
xlFilterValues, Criteria2:=Array(1, "10/1/2018")
The problem I have it in a pivot on the same data set. The date filter in the pivot looks like:
When I record the macro the code lookslike:
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = "10/1/2018" ' I tried with all dates. Showing Oct. here
When I try to run it back I get an error message in the last like like: Run-time error '1004': Application-defined or object-defined error.
I tried to change the date as dd/mm/yyyy (like the source) and dd-mmm-yy (like in the pivot) but it does not make a difference.
Any ideas on how to make it work? Also, I don't understand why when you record it the date it changes to mm/dd/yyyy.
excel vba excel-vba pivot
asked Nov 12 '18 at 10:01
Selrac
93121846
add a comment |
I have a sheet in Excel with a date column (dd/mm/yyyy)as follows:
A$Period Name
01/01/2018
01/02/2018
01/03/2018
When I look at the filter in the table I can see the dates as follows:
If I record a macro to filter this column I get the following code, which when I run it back it works correctly
ActiveSheet.Range("$A$1:$BP$5413").AutoFilter Field:=17, Operator:= _
xlFilterValues, Criteria2:=Array(1, "10/1/2018")
The problem I have it in a pivot on the same data set. The date filter in the pivot looks like:
When I record the macro the code lookslike:
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = "10/1/2018" ' I tried with all dates. Showing Oct. here
When I try to run it back I get an error message in the last like like: Run-time error '1004': Application-defined or object-defined error.
I tried to change the date as dd/mm/yyyy (like the source) and dd-mmm-yy (like in the pivot) but it does not make a difference.
Any ideas on how to make it work? Also, I don't understand why when you record it the date it changes to mm/dd/yyyy.
excel vba excel-vba pivot
asked Nov 12 '18 at 10:01
Selrac
93121846
I have a sheet in Excel with a date column (dd/mm/yyyy)as follows:
A$Period Name
01/01/2018
01/02/2018
01/03/2018
When I look at the filter in the table I can see the dates as follows:
If I record a macro to filter this column I get the following code, which when I run it back it works correctly
ActiveSheet.Range("$A$1:$BP$5413").AutoFilter Field:=17, Operator:= _
xlFilterValues, Criteria2:=Array(1, "10/1/2018")
The problem I have it in a pivot on the same data set. The date filter in the pivot looks like:
When I record the macro the code lookslike:
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = "10/1/2018" ' I tried with all dates. Showing Oct. here
When I try to run it back I get an error message in the last like like: Run-time error '1004': Application-defined or object-defined error.
I tried to change the date as dd/mm/yyyy (like the source) and dd-mmm-yy (like in the pivot) but it does not make a difference.
Any ideas on how to make it work? Also, I don't understand why when you record it the date it changes to mm/dd/yyyy.
excel vba excel-vba pivot
excel vba excel-vba pivot
asked Nov 12 '18 at 10:01
Selrac
93121846
asked Nov 12 '18 at 10:01
Selrac
93121846
asked Nov 12 '18 at 10:01
Selrac
93121846
asked Nov 12 '18 at 10:01
Selrac
93121846
asked Nov 12 '18 at 10:01
Selrac
93121846
93121846
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I expect it'll work if you use the ISO date format:
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = "2018-10-1"
answered Nov 12 '18 at 10:26
jkpieterse
2,1541313
That didn't work either, but I managed to make it work, (after testing all possible options) using CurrentPage = "1-Oct-18" (Format(DateVal, "d-mmm-yy"))
– Selrac
Nov 12 '18 at 12:39
1
Your example failed for me, the one I posted worked for me. Dates are challenging when trying to set filters from VBA.
– jkpieterse
Nov 12 '18 at 12:56
It seems a bit challenging, that is right. I noticed that if you record a macro and select multiple dates it gives you the date format you need to use.
– Selrac
Nov 14 '18 at 15:22
add a comment |
In case it helps some one with a similar problem.
I managed to make it work doing the following:
DateVal = "01/10/2018"
DateVal = Format(DateVal, "d-mmm-yy")
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = DateVal
answered Nov 12 '18 at 12:41
Selrac
93121846
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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',
autoActivateHeartbeat: false,
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%2fstackoverflow.com%2fquestions%2f53259775%2fwhy-does-the-dates-filter-does-not-work-in-a-pivot-when-recording-a-macro-in-exc%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
I expect it'll work if you use the ISO date format:
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = "2018-10-1"
answered Nov 12 '18 at 10:26
jkpieterse
2,1541313
That didn't work either, but I managed to make it work, (after testing all possible options) using CurrentPage = "1-Oct-18" (Format(DateVal, "d-mmm-yy"))
– Selrac
Nov 12 '18 at 12:39
1
Your example failed for me, the one I posted worked for me. Dates are challenging when trying to set filters from VBA.
– jkpieterse
Nov 12 '18 at 12:56
It seems a bit challenging, that is right. I noticed that if you record a macro and select multiple dates it gives you the date format you need to use.
– Selrac
Nov 14 '18 at 15:22
add a comment |
I expect it'll work if you use the ISO date format:
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = "2018-10-1"
answered Nov 12 '18 at 10:26
jkpieterse
2,1541313
That didn't work either, but I managed to make it work, (after testing all possible options) using CurrentPage = "1-Oct-18" (Format(DateVal, "d-mmm-yy"))
– Selrac
Nov 12 '18 at 12:39
1
Your example failed for me, the one I posted worked for me. Dates are challenging when trying to set filters from VBA.
– jkpieterse
Nov 12 '18 at 12:56
It seems a bit challenging, that is right. I noticed that if you record a macro and select multiple dates it gives you the date format you need to use.
– Selrac
Nov 14 '18 at 15:22
add a comment |
I expect it'll work if you use the ISO date format:
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = "2018-10-1"
answered Nov 12 '18 at 10:26
jkpieterse
2,1541313
I expect it'll work if you use the ISO date format:
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = "2018-10-1"
answered Nov 12 '18 at 10:26
jkpieterse
2,1541313
answered Nov 12 '18 at 10:26
jkpieterse
2,1541313
answered Nov 12 '18 at 10:26
jkpieterse
2,1541313
answered Nov 12 '18 at 10:26
jkpieterse
2,1541313
2,1541313
That didn't work either, but I managed to make it work, (after testing all possible options) using CurrentPage = "1-Oct-18" (Format(DateVal, "d-mmm-yy"))
– Selrac
Nov 12 '18 at 12:39
1
Your example failed for me, the one I posted worked for me. Dates are challenging when trying to set filters from VBA.
– jkpieterse
Nov 12 '18 at 12:56
It seems a bit challenging, that is right. I noticed that if you record a macro and select multiple dates it gives you the date format you need to use.
– Selrac
Nov 14 '18 at 15:22
add a comment |
That didn't work either, but I managed to make it work, (after testing all possible options) using CurrentPage = "1-Oct-18" (Format(DateVal, "d-mmm-yy"))
– Selrac
Nov 12 '18 at 12:39
1
Your example failed for me, the one I posted worked for me. Dates are challenging when trying to set filters from VBA.
– jkpieterse
Nov 12 '18 at 12:56
It seems a bit challenging, that is right. I noticed that if you record a macro and select multiple dates it gives you the date format you need to use.
– Selrac
Nov 14 '18 at 15:22
That didn't work either, but I managed to make it work, (after testing all possible options) using CurrentPage = "1-Oct-18" (Format(DateVal, "d-mmm-yy"))
– Selrac
Nov 12 '18 at 12:39
That didn't work either, but I managed to make it work, (after testing all possible options) using CurrentPage = "1-Oct-18" (Format(DateVal, "d-mmm-yy"))
– Selrac
Nov 12 '18 at 12:39
1
1
Your example failed for me, the one I posted worked for me. Dates are challenging when trying to set filters from VBA.
– jkpieterse
Nov 12 '18 at 12:56
Your example failed for me, the one I posted worked for me. Dates are challenging when trying to set filters from VBA.
– jkpieterse
Nov 12 '18 at 12:56
It seems a bit challenging, that is right. I noticed that if you record a macro and select multiple dates it gives you the date format you need to use.
– Selrac
Nov 14 '18 at 15:22
It seems a bit challenging, that is right. I noticed that if you record a macro and select multiple dates it gives you the date format you need to use.
– Selrac
Nov 14 '18 at 15:22
add a comment |
In case it helps some one with a similar problem.
I managed to make it work doing the following:
DateVal = "01/10/2018"
DateVal = Format(DateVal, "d-mmm-yy")
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = DateVal
answered Nov 12 '18 at 12:41
Selrac
93121846
add a comment |
In case it helps some one with a similar problem.
I managed to make it work doing the following:
DateVal = "01/10/2018"
DateVal = Format(DateVal, "d-mmm-yy")
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = DateVal
answered Nov 12 '18 at 12:41
Selrac
93121846
add a comment |
In case it helps some one with a similar problem.
I managed to make it work doing the following:
DateVal = "01/10/2018"
DateVal = Format(DateVal, "d-mmm-yy")
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = DateVal
answered Nov 12 '18 at 12:41
Selrac
93121846
In case it helps some one with a similar problem.
I managed to make it work doing the following:
DateVal = "01/10/2018"
DateVal = Format(DateVal, "d-mmm-yy")
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
CurrentPage = DateVal
answered Nov 12 '18 at 12:41
Selrac
93121846
answered Nov 12 '18 at 12:41
Selrac
93121846
answered Nov 12 '18 at 12:41
Selrac
93121846
answered Nov 12 '18 at 12:41
Selrac
93121846
93121846
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53259775%2fwhy-does-the-dates-filter-does-not-work-in-a-pivot-when-recording-a-macro-in-exc%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
