Can you assign index values to .Currentpage VBA/Excel?
I'm trying to create a Macro that will, among other things, set the .CurrentPage to a specific value. My question is can you use an index value instead of the name of one of the values?
ActiveSheet.PivotTables("PivotTable1").PivotFields("starttime").CurrentPage = "11/19/2018"
Here is an example line that I'm using. The problem is the dates will change every week to represent the new data. However there will always be five options, Monday to Friday. So instead of having to use the date, which I'll have to change every week, can I use an index value instead?
I.e. [0] for Monday and [4] for Friday etc. If this is possible then what is the correct syntax for this? I've looked all over the web and found nothing even remotely related to using index numbers instead of values.
excel vba
edited Nov 27 '18 at 16:12
T.M.
2,3053828
asked Nov 27 '18 at 15:30
S. TrottS. Trott
32
add a comment |
I'm trying to create a Macro that will, among other things, set the .CurrentPage to a specific value. My question is can you use an index value instead of the name of one of the values?
ActiveSheet.PivotTables("PivotTable1").PivotFields("starttime").CurrentPage = "11/19/2018"
Here is an example line that I'm using. The problem is the dates will change every week to represent the new data. However there will always be five options, Monday to Friday. So instead of having to use the date, which I'll have to change every week, can I use an index value instead?
I.e. [0] for Monday and [4] for Friday etc. If this is possible then what is the correct syntax for this? I've looked all over the web and found nothing even remotely related to using index numbers instead of values.
excel vba
edited Nov 27 '18 at 16:12
T.M.
2,3053828
asked Nov 27 '18 at 15:30
S. TrottS. Trott
32
add a comment |
I'm trying to create a Macro that will, among other things, set the .CurrentPage to a specific value. My question is can you use an index value instead of the name of one of the values?
ActiveSheet.PivotTables("PivotTable1").PivotFields("starttime").CurrentPage = "11/19/2018"
Here is an example line that I'm using. The problem is the dates will change every week to represent the new data. However there will always be five options, Monday to Friday. So instead of having to use the date, which I'll have to change every week, can I use an index value instead?
I.e. [0] for Monday and [4] for Friday etc. If this is possible then what is the correct syntax for this? I've looked all over the web and found nothing even remotely related to using index numbers instead of values.
excel vba
edited Nov 27 '18 at 16:12
T.M.
2,3053828
asked Nov 27 '18 at 15:30
S. TrottS. Trott
32
I'm trying to create a Macro that will, among other things, set the .CurrentPage to a specific value. My question is can you use an index value instead of the name of one of the values?
ActiveSheet.PivotTables("PivotTable1").PivotFields("starttime").CurrentPage = "11/19/2018"
Here is an example line that I'm using. The problem is the dates will change every week to represent the new data. However there will always be five options, Monday to Friday. So instead of having to use the date, which I'll have to change every week, can I use an index value instead?
I.e. [0] for Monday and [4] for Friday etc. If this is possible then what is the correct syntax for this? I've looked all over the web and found nothing even remotely related to using index numbers instead of values.
excel vba
excel vba
edited Nov 27 '18 at 16:12
T.M.
2,3053828
asked Nov 27 '18 at 15:30
S. TrottS. Trott
32
edited Nov 27 '18 at 16:12
T.M.
2,3053828
asked Nov 27 '18 at 15:30
S. TrottS. Trott
32
edited Nov 27 '18 at 16:12
T.M.
2,3053828
edited Nov 27 '18 at 16:12
T.M.
2,3053828
edited Nov 27 '18 at 16:12
T.M.
2,3053828
2,3053828
asked Nov 27 '18 at 15:30
S. TrottS. Trott
32
asked Nov 27 '18 at 15:30
S. TrottS. Trott
32
asked Nov 27 '18 at 15:30
S. TrottS. Trott
32
32
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
If you know that your field will only contain the relevant items and nothing else, then:
With ActiveSheet.PivotTables("PivotTable1").PivotFields("starttime")
.CurrentPage = .PivotItems(3).Value
End With
However pivot tables may retain items deleted from the data source. If that is how it is set up for you, then you will either need to remove cached items before using this code, or calculate that page value instead of looking it up in the list of available values, which should be not difficult given that it is based on the current week.
answered Nov 27 '18 at 17:47
GSergGSerg
59.8k15103227
Thanks, I will try this and get back to you. It's a fresh sheet every week thats exported from external software so I don't think the cache thing should be a problem
– S. Trott
Nov 29 '18 at 8:45
It gives me a Compile Error, Invaid or unqualified reference and it highlights .PivotItems
– S. Trott
Nov 29 '18 at 9:20
@S.Trott Did you lose theWithline?
– GSerg
Nov 29 '18 at 9:24
Apologies that was the case, I've added the With and End With and it now works correctly, thanks!
– S. Trott
Nov 29 '18 at 10:15
add a comment |
Something like this
Public arrDate(4) As Date
Sub CreateDateArray()
Dim i As Integer
For i = 0 To 4
arrDate(i) = DateAdd("d", -Weekday(Date, vbMonday) + (i + 1), Date)
Next i
End Sub
or something like this
Function DateFromIndex(intDay As Integer) As Date
DateFromIndex = DateAdd("d", -Weekday(Date, vbMonday) + (intDay+1), Date)
End Function
answered Nov 27 '18 at 15:38
Nathan_SavNathan_Sav
6,1861618
Thanks for your help but I don't think I need to mess around with dates. To my understanding all its currently doing is searching for a text based value. (11/19/2018) as text. There are always five values and they're always in order by smallest to largest so I'm just wondering if there's a way to select the first index instead of querying it for the actual date. prntscr.com/lnoa2f Here is an image to help explain, these are the options under the filter in the pivot table, do these correspond to indexes or do I have to use the exact value
– S. Trott
Nov 27 '18 at 16:07
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%2f53502979%2fcan-you-assign-index-values-to-currentpage-vba-excel%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
If you know that your field will only contain the relevant items and nothing else, then:
With ActiveSheet.PivotTables("PivotTable1").PivotFields("starttime")
.CurrentPage = .PivotItems(3).Value
End With
However pivot tables may retain items deleted from the data source. If that is how it is set up for you, then you will either need to remove cached items before using this code, or calculate that page value instead of looking it up in the list of available values, which should be not difficult given that it is based on the current week.
answered Nov 27 '18 at 17:47
GSergGSerg
59.8k15103227
Thanks, I will try this and get back to you. It's a fresh sheet every week thats exported from external software so I don't think the cache thing should be a problem
– S. Trott
Nov 29 '18 at 8:45
It gives me a Compile Error, Invaid or unqualified reference and it highlights .PivotItems
– S. Trott
Nov 29 '18 at 9:20
@S.Trott Did you lose theWithline?
– GSerg
Nov 29 '18 at 9:24
Apologies that was the case, I've added the With and End With and it now works correctly, thanks!
– S. Trott
Nov 29 '18 at 10:15
add a comment |
If you know that your field will only contain the relevant items and nothing else, then:
With ActiveSheet.PivotTables("PivotTable1").PivotFields("starttime")
.CurrentPage = .PivotItems(3).Value
End With
However pivot tables may retain items deleted from the data source. If that is how it is set up for you, then you will either need to remove cached items before using this code, or calculate that page value instead of looking it up in the list of available values, which should be not difficult given that it is based on the current week.
answered Nov 27 '18 at 17:47
GSergGSerg
59.8k15103227
Thanks, I will try this and get back to you. It's a fresh sheet every week thats exported from external software so I don't think the cache thing should be a problem
– S. Trott
Nov 29 '18 at 8:45
It gives me a Compile Error, Invaid or unqualified reference and it highlights .PivotItems
– S. Trott
Nov 29 '18 at 9:20
@S.Trott Did you lose theWithline?
– GSerg
Nov 29 '18 at 9:24
Apologies that was the case, I've added the With and End With and it now works correctly, thanks!
– S. Trott
Nov 29 '18 at 10:15
add a comment |
If you know that your field will only contain the relevant items and nothing else, then:
With ActiveSheet.PivotTables("PivotTable1").PivotFields("starttime")
.CurrentPage = .PivotItems(3).Value
End With
However pivot tables may retain items deleted from the data source. If that is how it is set up for you, then you will either need to remove cached items before using this code, or calculate that page value instead of looking it up in the list of available values, which should be not difficult given that it is based on the current week.
answered Nov 27 '18 at 17:47
GSergGSerg
59.8k15103227
If you know that your field will only contain the relevant items and nothing else, then:
With ActiveSheet.PivotTables("PivotTable1").PivotFields("starttime")
.CurrentPage = .PivotItems(3).Value
End With
However pivot tables may retain items deleted from the data source. If that is how it is set up for you, then you will either need to remove cached items before using this code, or calculate that page value instead of looking it up in the list of available values, which should be not difficult given that it is based on the current week.
answered Nov 27 '18 at 17:47
GSergGSerg
59.8k15103227
edited Nov 27 '18 at 17:57
answered Nov 27 '18 at 17:47
GSergGSerg
59.8k15103227
answered Nov 27 '18 at 17:47
GSergGSerg
59.8k15103227
answered Nov 27 '18 at 17:47
GSergGSerg
59.8k15103227
59.8k15103227
Thanks, I will try this and get back to you. It's a fresh sheet every week thats exported from external software so I don't think the cache thing should be a problem
– S. Trott
Nov 29 '18 at 8:45
It gives me a Compile Error, Invaid or unqualified reference and it highlights .PivotItems
– S. Trott
Nov 29 '18 at 9:20
@S.Trott Did you lose theWithline?
– GSerg
Nov 29 '18 at 9:24
Apologies that was the case, I've added the With and End With and it now works correctly, thanks!
– S. Trott
Nov 29 '18 at 10:15
add a comment |
Thanks, I will try this and get back to you. It's a fresh sheet every week thats exported from external software so I don't think the cache thing should be a problem
– S. Trott
Nov 29 '18 at 8:45
It gives me a Compile Error, Invaid or unqualified reference and it highlights .PivotItems
– S. Trott
Nov 29 '18 at 9:20
@S.Trott Did you lose theWithline?
– GSerg
Nov 29 '18 at 9:24
Apologies that was the case, I've added the With and End With and it now works correctly, thanks!
– S. Trott
Nov 29 '18 at 10:15
Thanks, I will try this and get back to you. It's a fresh sheet every week thats exported from external software so I don't think the cache thing should be a problem
– S. Trott
Nov 29 '18 at 8:45
Thanks, I will try this and get back to you. It's a fresh sheet every week thats exported from external software so I don't think the cache thing should be a problem
– S. Trott
Nov 29 '18 at 8:45
It gives me a Compile Error, Invaid or unqualified reference and it highlights .PivotItems
– S. Trott
Nov 29 '18 at 9:20
It gives me a Compile Error, Invaid or unqualified reference and it highlights .PivotItems
– S. Trott
Nov 29 '18 at 9:20
@S.Trott Did you lose the
With line?– GSerg
Nov 29 '18 at 9:24
@S.Trott Did you lose the
With line?– GSerg
Nov 29 '18 at 9:24
Apologies that was the case, I've added the With and End With and it now works correctly, thanks!
– S. Trott
Nov 29 '18 at 10:15
Apologies that was the case, I've added the With and End With and it now works correctly, thanks!
– S. Trott
Nov 29 '18 at 10:15
add a comment |
Something like this
Public arrDate(4) As Date
Sub CreateDateArray()
Dim i As Integer
For i = 0 To 4
arrDate(i) = DateAdd("d", -Weekday(Date, vbMonday) + (i + 1), Date)
Next i
End Sub
or something like this
Function DateFromIndex(intDay As Integer) As Date
DateFromIndex = DateAdd("d", -Weekday(Date, vbMonday) + (intDay+1), Date)
End Function
answered Nov 27 '18 at 15:38
Nathan_SavNathan_Sav
6,1861618
Thanks for your help but I don't think I need to mess around with dates. To my understanding all its currently doing is searching for a text based value. (11/19/2018) as text. There are always five values and they're always in order by smallest to largest so I'm just wondering if there's a way to select the first index instead of querying it for the actual date. prntscr.com/lnoa2f Here is an image to help explain, these are the options under the filter in the pivot table, do these correspond to indexes or do I have to use the exact value
– S. Trott
Nov 27 '18 at 16:07
add a comment |
Something like this
Public arrDate(4) As Date
Sub CreateDateArray()
Dim i As Integer
For i = 0 To 4
arrDate(i) = DateAdd("d", -Weekday(Date, vbMonday) + (i + 1), Date)
Next i
End Sub
or something like this
Function DateFromIndex(intDay As Integer) As Date
DateFromIndex = DateAdd("d", -Weekday(Date, vbMonday) + (intDay+1), Date)
End Function
answered Nov 27 '18 at 15:38
Nathan_SavNathan_Sav
6,1861618
Thanks for your help but I don't think I need to mess around with dates. To my understanding all its currently doing is searching for a text based value. (11/19/2018) as text. There are always five values and they're always in order by smallest to largest so I'm just wondering if there's a way to select the first index instead of querying it for the actual date. prntscr.com/lnoa2f Here is an image to help explain, these are the options under the filter in the pivot table, do these correspond to indexes or do I have to use the exact value
– S. Trott
Nov 27 '18 at 16:07
add a comment |
Something like this
Public arrDate(4) As Date
Sub CreateDateArray()
Dim i As Integer
For i = 0 To 4
arrDate(i) = DateAdd("d", -Weekday(Date, vbMonday) + (i + 1), Date)
Next i
End Sub
or something like this
Function DateFromIndex(intDay As Integer) As Date
DateFromIndex = DateAdd("d", -Weekday(Date, vbMonday) + (intDay+1), Date)
End Function
answered Nov 27 '18 at 15:38
Nathan_SavNathan_Sav
6,1861618
Something like this
Public arrDate(4) As Date
Sub CreateDateArray()
Dim i As Integer
For i = 0 To 4
arrDate(i) = DateAdd("d", -Weekday(Date, vbMonday) + (i + 1), Date)
Next i
End Sub
or something like this
Function DateFromIndex(intDay As Integer) As Date
DateFromIndex = DateAdd("d", -Weekday(Date, vbMonday) + (intDay+1), Date)
End Function
answered Nov 27 '18 at 15:38
Nathan_SavNathan_Sav
6,1861618
edited Nov 27 '18 at 15:43
answered Nov 27 '18 at 15:38
Nathan_SavNathan_Sav
6,1861618
answered Nov 27 '18 at 15:38
Nathan_SavNathan_Sav
6,1861618
answered Nov 27 '18 at 15:38
Nathan_SavNathan_Sav
6,1861618
6,1861618
Thanks for your help but I don't think I need to mess around with dates. To my understanding all its currently doing is searching for a text based value. (11/19/2018) as text. There are always five values and they're always in order by smallest to largest so I'm just wondering if there's a way to select the first index instead of querying it for the actual date. prntscr.com/lnoa2f Here is an image to help explain, these are the options under the filter in the pivot table, do these correspond to indexes or do I have to use the exact value
– S. Trott
Nov 27 '18 at 16:07
add a comment |
Thanks for your help but I don't think I need to mess around with dates. To my understanding all its currently doing is searching for a text based value. (11/19/2018) as text. There are always five values and they're always in order by smallest to largest so I'm just wondering if there's a way to select the first index instead of querying it for the actual date. prntscr.com/lnoa2f Here is an image to help explain, these are the options under the filter in the pivot table, do these correspond to indexes or do I have to use the exact value
– S. Trott
Nov 27 '18 at 16:07
Thanks for your help but I don't think I need to mess around with dates. To my understanding all its currently doing is searching for a text based value. (11/19/2018) as text. There are always five values and they're always in order by smallest to largest so I'm just wondering if there's a way to select the first index instead of querying it for the actual date. prntscr.com/lnoa2f Here is an image to help explain, these are the options under the filter in the pivot table, do these correspond to indexes or do I have to use the exact value
– S. Trott
Nov 27 '18 at 16:07
Thanks for your help but I don't think I need to mess around with dates. To my understanding all its currently doing is searching for a text based value. (11/19/2018) as text. There are always five values and they're always in order by smallest to largest so I'm just wondering if there's a way to select the first index instead of querying it for the actual date. prntscr.com/lnoa2f Here is an image to help explain, these are the options under the filter in the pivot table, do these correspond to indexes or do I have to use the exact value
– S. Trott
Nov 27 '18 at 16:07
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.
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%2f53502979%2fcan-you-assign-index-values-to-currentpage-vba-excel%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
