How to Open a Worksheet at TODAY()+2?
I have multiple Excel worksheets with dates in col B. I use the following VBA code block to open the worksheet with today's date visible on screen.
Private Sub Worksheet_Activate()
Dim d As Date, i As Long
d = Date
ActiveWindow.ScrollRow = 1
For i = 16 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = d Then
Cells(i, "B").Select
Exit Sub
End If
Next
End Sub
The date column does not contain dates for the weekend so the coding only works when today is between Monday and Friday. If I open the sheet on a Saturday it opens at the top of the sheet, which can be thousands of rows away from today's date as the sheet contains historical data dating back several years.
How do I amend the coding so that if today's date is not in col B it increments today's date by 2 and searches for the VBA equivalent of TODAY()+2, so that it will open the sheet with Monday's date visible if today is Saturday, or Tuesday's date visible if today is Sunday?
excel vba
asked Nov 16 '18 at 23:27
OldFellaOldFella
112
add a comment |
I have multiple Excel worksheets with dates in col B. I use the following VBA code block to open the worksheet with today's date visible on screen.
Private Sub Worksheet_Activate()
Dim d As Date, i As Long
d = Date
ActiveWindow.ScrollRow = 1
For i = 16 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = d Then
Cells(i, "B").Select
Exit Sub
End If
Next
End Sub
The date column does not contain dates for the weekend so the coding only works when today is between Monday and Friday. If I open the sheet on a Saturday it opens at the top of the sheet, which can be thousands of rows away from today's date as the sheet contains historical data dating back several years.
How do I amend the coding so that if today's date is not in col B it increments today's date by 2 and searches for the VBA equivalent of TODAY()+2, so that it will open the sheet with Monday's date visible if today is Saturday, or Tuesday's date visible if today is Sunday?
excel vba
asked Nov 16 '18 at 23:27
OldFellaOldFella
112
add a comment |
I have multiple Excel worksheets with dates in col B. I use the following VBA code block to open the worksheet with today's date visible on screen.
Private Sub Worksheet_Activate()
Dim d As Date, i As Long
d = Date
ActiveWindow.ScrollRow = 1
For i = 16 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = d Then
Cells(i, "B").Select
Exit Sub
End If
Next
End Sub
The date column does not contain dates for the weekend so the coding only works when today is between Monday and Friday. If I open the sheet on a Saturday it opens at the top of the sheet, which can be thousands of rows away from today's date as the sheet contains historical data dating back several years.
How do I amend the coding so that if today's date is not in col B it increments today's date by 2 and searches for the VBA equivalent of TODAY()+2, so that it will open the sheet with Monday's date visible if today is Saturday, or Tuesday's date visible if today is Sunday?
excel vba
asked Nov 16 '18 at 23:27
OldFellaOldFella
112
I have multiple Excel worksheets with dates in col B. I use the following VBA code block to open the worksheet with today's date visible on screen.
Private Sub Worksheet_Activate()
Dim d As Date, i As Long
d = Date
ActiveWindow.ScrollRow = 1
For i = 16 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = d Then
Cells(i, "B").Select
Exit Sub
End If
Next
End Sub
The date column does not contain dates for the weekend so the coding only works when today is between Monday and Friday. If I open the sheet on a Saturday it opens at the top of the sheet, which can be thousands of rows away from today's date as the sheet contains historical data dating back several years.
How do I amend the coding so that if today's date is not in col B it increments today's date by 2 and searches for the VBA equivalent of TODAY()+2, so that it will open the sheet with Monday's date visible if today is Saturday, or Tuesday's date visible if today is Sunday?
excel vba
excel vba
asked Nov 16 '18 at 23:27
OldFellaOldFella
112
asked Nov 16 '18 at 23:27
OldFellaOldFella
112
asked Nov 16 '18 at 23:27
OldFellaOldFella
112
asked Nov 16 '18 at 23:27
OldFellaOldFella
112
asked Nov 16 '18 at 23:27
OldFellaOldFella
112
112
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Not sure if this is what you're looking for, but if it's a weekend, it'll increment the date by two; otherwise it will remain as today's date.
Replace:
d = Date
with
d=Evaluate("=IF(OR(WEEKDAY(TODAY())=7,WEEKDAY(TODAY())=1),TODAY()+2,TODAY())")
But also you may want to reconsider looping through every cell to find what you're looking for. If you have a large dataset it might take a while consider replacing it with this:
If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B:B"), d) > 0 Then
ActiveSheet.Range("B:B").Find(d).Activate
End If
the countif just ensures that the cell value actually exists and doesn't error when you try to activate it.
answered Nov 16 '18 at 23:43
BrotatoBrotato
800212
add a comment |
Disregard! After more experimenting I've managed to resolve it, it simply needed 1 more line of code:
Private Sub Worksheet_Activate()
Dim d As Date, i As Long
d = Date
ActiveWindow.ScrollRow = 1
For i = 16 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = d Then
Cells(i, "B").Select
Exit Sub
Else: d = Date + 2
End If
Next
End Sub
answered Nov 16 '18 at 23:49
OldFellaOldFella
112
I feel like this might not work all the time. Imagine a scenario where its Saturday, andB16contains Monday's date. If you run this, it'll incrementdby two, but ifB17doesn't also contain Monday's date it won't match and it'll increment to Wednesday's date and it'll continue on like that. A similar thing would happen during the weekdays ifB16didn't contain the current day's date.
– Brotato
Nov 16 '18 at 23:59
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%2f53346678%2fhow-to-open-a-worksheet-at-today2%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
Not sure if this is what you're looking for, but if it's a weekend, it'll increment the date by two; otherwise it will remain as today's date.
Replace:
d = Date
with
d=Evaluate("=IF(OR(WEEKDAY(TODAY())=7,WEEKDAY(TODAY())=1),TODAY()+2,TODAY())")
But also you may want to reconsider looping through every cell to find what you're looking for. If you have a large dataset it might take a while consider replacing it with this:
If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B:B"), d) > 0 Then
ActiveSheet.Range("B:B").Find(d).Activate
End If
the countif just ensures that the cell value actually exists and doesn't error when you try to activate it.
answered Nov 16 '18 at 23:43
BrotatoBrotato
800212
add a comment |
Not sure if this is what you're looking for, but if it's a weekend, it'll increment the date by two; otherwise it will remain as today's date.
Replace:
d = Date
with
d=Evaluate("=IF(OR(WEEKDAY(TODAY())=7,WEEKDAY(TODAY())=1),TODAY()+2,TODAY())")
But also you may want to reconsider looping through every cell to find what you're looking for. If you have a large dataset it might take a while consider replacing it with this:
If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B:B"), d) > 0 Then
ActiveSheet.Range("B:B").Find(d).Activate
End If
the countif just ensures that the cell value actually exists and doesn't error when you try to activate it.
answered Nov 16 '18 at 23:43
BrotatoBrotato
800212
add a comment |
Not sure if this is what you're looking for, but if it's a weekend, it'll increment the date by two; otherwise it will remain as today's date.
Replace:
d = Date
with
d=Evaluate("=IF(OR(WEEKDAY(TODAY())=7,WEEKDAY(TODAY())=1),TODAY()+2,TODAY())")
But also you may want to reconsider looping through every cell to find what you're looking for. If you have a large dataset it might take a while consider replacing it with this:
If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B:B"), d) > 0 Then
ActiveSheet.Range("B:B").Find(d).Activate
End If
the countif just ensures that the cell value actually exists and doesn't error when you try to activate it.
answered Nov 16 '18 at 23:43
BrotatoBrotato
800212
Not sure if this is what you're looking for, but if it's a weekend, it'll increment the date by two; otherwise it will remain as today's date.
Replace:
d = Date
with
d=Evaluate("=IF(OR(WEEKDAY(TODAY())=7,WEEKDAY(TODAY())=1),TODAY()+2,TODAY())")
But also you may want to reconsider looping through every cell to find what you're looking for. If you have a large dataset it might take a while consider replacing it with this:
If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B:B"), d) > 0 Then
ActiveSheet.Range("B:B").Find(d).Activate
End If
the countif just ensures that the cell value actually exists and doesn't error when you try to activate it.
answered Nov 16 '18 at 23:43
BrotatoBrotato
800212
edited Nov 16 '18 at 23:50
answered Nov 16 '18 at 23:43
BrotatoBrotato
800212
answered Nov 16 '18 at 23:43
BrotatoBrotato
800212
answered Nov 16 '18 at 23:43
BrotatoBrotato
800212
800212
add a comment |
add a comment |
Disregard! After more experimenting I've managed to resolve it, it simply needed 1 more line of code:
Private Sub Worksheet_Activate()
Dim d As Date, i As Long
d = Date
ActiveWindow.ScrollRow = 1
For i = 16 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = d Then
Cells(i, "B").Select
Exit Sub
Else: d = Date + 2
End If
Next
End Sub
answered Nov 16 '18 at 23:49
OldFellaOldFella
112
I feel like this might not work all the time. Imagine a scenario where its Saturday, andB16contains Monday's date. If you run this, it'll incrementdby two, but ifB17doesn't also contain Monday's date it won't match and it'll increment to Wednesday's date and it'll continue on like that. A similar thing would happen during the weekdays ifB16didn't contain the current day's date.
– Brotato
Nov 16 '18 at 23:59
add a comment |
Disregard! After more experimenting I've managed to resolve it, it simply needed 1 more line of code:
Private Sub Worksheet_Activate()
Dim d As Date, i As Long
d = Date
ActiveWindow.ScrollRow = 1
For i = 16 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = d Then
Cells(i, "B").Select
Exit Sub
Else: d = Date + 2
End If
Next
End Sub
answered Nov 16 '18 at 23:49
OldFellaOldFella
112
I feel like this might not work all the time. Imagine a scenario where its Saturday, andB16contains Monday's date. If you run this, it'll incrementdby two, but ifB17doesn't also contain Monday's date it won't match and it'll increment to Wednesday's date and it'll continue on like that. A similar thing would happen during the weekdays ifB16didn't contain the current day's date.
– Brotato
Nov 16 '18 at 23:59
add a comment |
Disregard! After more experimenting I've managed to resolve it, it simply needed 1 more line of code:
Private Sub Worksheet_Activate()
Dim d As Date, i As Long
d = Date
ActiveWindow.ScrollRow = 1
For i = 16 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = d Then
Cells(i, "B").Select
Exit Sub
Else: d = Date + 2
End If
Next
End Sub
answered Nov 16 '18 at 23:49
OldFellaOldFella
112
Disregard! After more experimenting I've managed to resolve it, it simply needed 1 more line of code:
Private Sub Worksheet_Activate()
Dim d As Date, i As Long
d = Date
ActiveWindow.ScrollRow = 1
For i = 16 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = d Then
Cells(i, "B").Select
Exit Sub
Else: d = Date + 2
End If
Next
End Sub
answered Nov 16 '18 at 23:49
OldFellaOldFella
112
answered Nov 16 '18 at 23:49
OldFellaOldFella
112
answered Nov 16 '18 at 23:49
OldFellaOldFella
112
answered Nov 16 '18 at 23:49
OldFellaOldFella
112
112
I feel like this might not work all the time. Imagine a scenario where its Saturday, andB16contains Monday's date. If you run this, it'll incrementdby two, but ifB17doesn't also contain Monday's date it won't match and it'll increment to Wednesday's date and it'll continue on like that. A similar thing would happen during the weekdays ifB16didn't contain the current day's date.
– Brotato
Nov 16 '18 at 23:59
add a comment |
I feel like this might not work all the time. Imagine a scenario where its Saturday, andB16contains Monday's date. If you run this, it'll incrementdby two, but ifB17doesn't also contain Monday's date it won't match and it'll increment to Wednesday's date and it'll continue on like that. A similar thing would happen during the weekdays ifB16didn't contain the current day's date.
– Brotato
Nov 16 '18 at 23:59
I feel like this might not work all the time. Imagine a scenario where its Saturday, and
B16 contains Monday's date. If you run this, it'll increment d by two, but if B17 doesn't also contain Monday's date it won't match and it'll increment to Wednesday's date and it'll continue on like that. A similar thing would happen during the weekdays if B16 didn't contain the current day's date.– Brotato
Nov 16 '18 at 23:59
I feel like this might not work all the time. Imagine a scenario where its Saturday, and
B16 contains Monday's date. If you run this, it'll increment d by two, but if B17 doesn't also contain Monday's date it won't match and it'll increment to Wednesday's date and it'll continue on like that. A similar thing would happen during the weekdays if B16 didn't contain the current day's date.– Brotato
Nov 16 '18 at 23:59
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%2f53346678%2fhow-to-open-a-worksheet-at-today2%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
