Selecting a range of cells
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am attempting to change sheet names while increasing selected dates by one (1) year.
OCT is the beginning of a new fiscal year (FY) and I'm trying to adjust accordingly. For example OCT-17, NOV-17, DEC-17, JAN-18, etc. I'm trying to change to OCT-18, NOV-18, DEC-18, JAN-19 in order to clear previous data and enter the new FY information.
Thus far, I have been able to adjust sheet names, however I am stumbling on being able to "select" the range of dates that I am attempting to adjust for the new FY. I am attempting to select the range of dates and add one (1) year to each of the dates in order to reference accurate data as the table references a pivot table as its data source.
Dim MyDate As String
Dim Cell as Range
MyDate=Format(DateSerial(Year(Date), Month(10), 1, "yy")
If FormMonth = "OCT" then
sheet1.name = "FY" & MYDate - 3
sheet1.range("B9:M9").select
For Each Cell in selection
cell.value = DateAdd("yyyy", 1, CDate(cell.value))
Next cell
End If
I have MyDate - 3 to change the sheet names as I have separate sheets that hold the previous 3 years of FY data. That successfully changes the year to the FY information I would like to present.
My script is not liking the sheet1.range("B9:M9").select.
excel vba
edited Dec 23 '18 at 20:36
Community♦
11
asked Nov 16 '18 at 15:42
Derek HardenDerek Harden
215
add a comment |
I am attempting to change sheet names while increasing selected dates by one (1) year.
OCT is the beginning of a new fiscal year (FY) and I'm trying to adjust accordingly. For example OCT-17, NOV-17, DEC-17, JAN-18, etc. I'm trying to change to OCT-18, NOV-18, DEC-18, JAN-19 in order to clear previous data and enter the new FY information.
Thus far, I have been able to adjust sheet names, however I am stumbling on being able to "select" the range of dates that I am attempting to adjust for the new FY. I am attempting to select the range of dates and add one (1) year to each of the dates in order to reference accurate data as the table references a pivot table as its data source.
Dim MyDate As String
Dim Cell as Range
MyDate=Format(DateSerial(Year(Date), Month(10), 1, "yy")
If FormMonth = "OCT" then
sheet1.name = "FY" & MYDate - 3
sheet1.range("B9:M9").select
For Each Cell in selection
cell.value = DateAdd("yyyy", 1, CDate(cell.value))
Next cell
End If
I have MyDate - 3 to change the sheet names as I have separate sheets that hold the previous 3 years of FY data. That successfully changes the year to the FY information I would like to present.
My script is not liking the sheet1.range("B9:M9").select.
excel vba
edited Dec 23 '18 at 20:36
Community♦
11
asked Nov 16 '18 at 15:42
Derek HardenDerek Harden
215
Depending on what you're actually trying to so, you probably don't need toSelectany cells at all. IsOption Explicitspecified at the top of the module? What error are you getting? "my script is not liking {line of code}" doesn't say much about the problem you're having.
– Mathieu Guindon
Nov 16 '18 at 16:33
add a comment |
I am attempting to change sheet names while increasing selected dates by one (1) year.
OCT is the beginning of a new fiscal year (FY) and I'm trying to adjust accordingly. For example OCT-17, NOV-17, DEC-17, JAN-18, etc. I'm trying to change to OCT-18, NOV-18, DEC-18, JAN-19 in order to clear previous data and enter the new FY information.
Thus far, I have been able to adjust sheet names, however I am stumbling on being able to "select" the range of dates that I am attempting to adjust for the new FY. I am attempting to select the range of dates and add one (1) year to each of the dates in order to reference accurate data as the table references a pivot table as its data source.
Dim MyDate As String
Dim Cell as Range
MyDate=Format(DateSerial(Year(Date), Month(10), 1, "yy")
If FormMonth = "OCT" then
sheet1.name = "FY" & MYDate - 3
sheet1.range("B9:M9").select
For Each Cell in selection
cell.value = DateAdd("yyyy", 1, CDate(cell.value))
Next cell
End If
I have MyDate - 3 to change the sheet names as I have separate sheets that hold the previous 3 years of FY data. That successfully changes the year to the FY information I would like to present.
My script is not liking the sheet1.range("B9:M9").select.
excel vba
edited Dec 23 '18 at 20:36
Community♦
11
asked Nov 16 '18 at 15:42
Derek HardenDerek Harden
215
I am attempting to change sheet names while increasing selected dates by one (1) year.
OCT is the beginning of a new fiscal year (FY) and I'm trying to adjust accordingly. For example OCT-17, NOV-17, DEC-17, JAN-18, etc. I'm trying to change to OCT-18, NOV-18, DEC-18, JAN-19 in order to clear previous data and enter the new FY information.
Thus far, I have been able to adjust sheet names, however I am stumbling on being able to "select" the range of dates that I am attempting to adjust for the new FY. I am attempting to select the range of dates and add one (1) year to each of the dates in order to reference accurate data as the table references a pivot table as its data source.
Dim MyDate As String
Dim Cell as Range
MyDate=Format(DateSerial(Year(Date), Month(10), 1, "yy")
If FormMonth = "OCT" then
sheet1.name = "FY" & MYDate - 3
sheet1.range("B9:M9").select
For Each Cell in selection
cell.value = DateAdd("yyyy", 1, CDate(cell.value))
Next cell
End If
I have MyDate - 3 to change the sheet names as I have separate sheets that hold the previous 3 years of FY data. That successfully changes the year to the FY information I would like to present.
My script is not liking the sheet1.range("B9:M9").select.
excel vba
excel vba
edited Dec 23 '18 at 20:36
Community♦
11
asked Nov 16 '18 at 15:42
Derek HardenDerek Harden
215
edited Dec 23 '18 at 20:36
Community♦
11
asked Nov 16 '18 at 15:42
Derek HardenDerek Harden
215
edited Dec 23 '18 at 20:36
Community♦
11
edited Dec 23 '18 at 20:36
Community♦
11
edited Dec 23 '18 at 20:36
Community♦
11
11
asked Nov 16 '18 at 15:42
Derek HardenDerek Harden
215
asked Nov 16 '18 at 15:42
Derek HardenDerek Harden
215
asked Nov 16 '18 at 15:42
Derek HardenDerek Harden
215
215
Depending on what you're actually trying to so, you probably don't need toSelectany cells at all. IsOption Explicitspecified at the top of the module? What error are you getting? "my script is not liking {line of code}" doesn't say much about the problem you're having.
– Mathieu Guindon
Nov 16 '18 at 16:33
add a comment |
Depending on what you're actually trying to so, you probably don't need toSelectany cells at all. IsOption Explicitspecified at the top of the module? What error are you getting? "my script is not liking {line of code}" doesn't say much about the problem you're having.
– Mathieu Guindon
Nov 16 '18 at 16:33
Depending on what you're actually trying to so, you probably don't need to
Select any cells at all. Is Option Explicit specified at the top of the module? What error are you getting? "my script is not liking {line of code}" doesn't say much about the problem you're having.– Mathieu Guindon
Nov 16 '18 at 16:33
Depending on what you're actually trying to so, you probably don't need to
Select any cells at all. Is Option Explicit specified at the top of the module? What error are you getting? "my script is not liking {line of code}" doesn't say much about the problem you're having.– Mathieu Guindon
Nov 16 '18 at 16:33
add a comment |
1 Answer
1
active
oldest
votes
You need to set sheet1 to a worksheet:
mySheetName = "FY" & MYDate - 3
Set sheet1 = Worksheets(mySheetName)
That said, you really want to avoid using Activate/Select in your code. Something like:
For Each Cell in sheet1.range("B9:M9")
cell.value = DateAdd("yyyy", 1, CDate(cell.value))
Next cell
edited Nov 16 '18 at 16:35
Mathieu Guindon
44.8k770157
answered Nov 16 '18 at 15:45
cybernetic.nomadcybernetic.nomad
2,99621222
1
Thank you! It turns out I was attempting to reference the sheets' codenames, however I was referring to their index numbers. Solved, appreciate the advice. Still on the learning curve and am starting to figure some tricks out to avoid all of the activates and selections!
– Derek Harden
Nov 16 '18 at 18:28
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%2f53341097%2fselecting-a-range-of-cells%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need to set sheet1 to a worksheet:
mySheetName = "FY" & MYDate - 3
Set sheet1 = Worksheets(mySheetName)
That said, you really want to avoid using Activate/Select in your code. Something like:
For Each Cell in sheet1.range("B9:M9")
cell.value = DateAdd("yyyy", 1, CDate(cell.value))
Next cell
edited Nov 16 '18 at 16:35
Mathieu Guindon
44.8k770157
answered Nov 16 '18 at 15:45
cybernetic.nomadcybernetic.nomad
2,99621222
1
Thank you! It turns out I was attempting to reference the sheets' codenames, however I was referring to their index numbers. Solved, appreciate the advice. Still on the learning curve and am starting to figure some tricks out to avoid all of the activates and selections!
– Derek Harden
Nov 16 '18 at 18:28
add a comment |
You need to set sheet1 to a worksheet:
mySheetName = "FY" & MYDate - 3
Set sheet1 = Worksheets(mySheetName)
That said, you really want to avoid using Activate/Select in your code. Something like:
For Each Cell in sheet1.range("B9:M9")
cell.value = DateAdd("yyyy", 1, CDate(cell.value))
Next cell
edited Nov 16 '18 at 16:35
Mathieu Guindon
44.8k770157
answered Nov 16 '18 at 15:45
cybernetic.nomadcybernetic.nomad
2,99621222
1
Thank you! It turns out I was attempting to reference the sheets' codenames, however I was referring to their index numbers. Solved, appreciate the advice. Still on the learning curve and am starting to figure some tricks out to avoid all of the activates and selections!
– Derek Harden
Nov 16 '18 at 18:28
add a comment |
You need to set sheet1 to a worksheet:
mySheetName = "FY" & MYDate - 3
Set sheet1 = Worksheets(mySheetName)
That said, you really want to avoid using Activate/Select in your code. Something like:
For Each Cell in sheet1.range("B9:M9")
cell.value = DateAdd("yyyy", 1, CDate(cell.value))
Next cell
edited Nov 16 '18 at 16:35
Mathieu Guindon
44.8k770157
answered Nov 16 '18 at 15:45
cybernetic.nomadcybernetic.nomad
2,99621222
You need to set sheet1 to a worksheet:
mySheetName = "FY" & MYDate - 3
Set sheet1 = Worksheets(mySheetName)
That said, you really want to avoid using Activate/Select in your code. Something like:
For Each Cell in sheet1.range("B9:M9")
cell.value = DateAdd("yyyy", 1, CDate(cell.value))
Next cell
edited Nov 16 '18 at 16:35
Mathieu Guindon
44.8k770157
answered Nov 16 '18 at 15:45
cybernetic.nomadcybernetic.nomad
2,99621222
edited Nov 16 '18 at 16:35
Mathieu Guindon
44.8k770157
edited Nov 16 '18 at 16:35
Mathieu Guindon
44.8k770157
edited Nov 16 '18 at 16:35
Mathieu Guindon
44.8k770157
44.8k770157
answered Nov 16 '18 at 15:45
cybernetic.nomadcybernetic.nomad
2,99621222
answered Nov 16 '18 at 15:45
cybernetic.nomadcybernetic.nomad
2,99621222
answered Nov 16 '18 at 15:45
cybernetic.nomadcybernetic.nomad
2,99621222
2,99621222
1
Thank you! It turns out I was attempting to reference the sheets' codenames, however I was referring to their index numbers. Solved, appreciate the advice. Still on the learning curve and am starting to figure some tricks out to avoid all of the activates and selections!
– Derek Harden
Nov 16 '18 at 18:28
add a comment |
1
Thank you! It turns out I was attempting to reference the sheets' codenames, however I was referring to their index numbers. Solved, appreciate the advice. Still on the learning curve and am starting to figure some tricks out to avoid all of the activates and selections!
– Derek Harden
Nov 16 '18 at 18:28
1
1
Thank you! It turns out I was attempting to reference the sheets' codenames, however I was referring to their index numbers. Solved, appreciate the advice. Still on the learning curve and am starting to figure some tricks out to avoid all of the activates and selections!
– Derek Harden
Nov 16 '18 at 18:28
Thank you! It turns out I was attempting to reference the sheets' codenames, however I was referring to their index numbers. Solved, appreciate the advice. Still on the learning curve and am starting to figure some tricks out to avoid all of the activates and selections!
– Derek Harden
Nov 16 '18 at 18:28
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%2f53341097%2fselecting-a-range-of-cells%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
Depending on what you're actually trying to so, you probably don't need to
Selectany cells at all. IsOption Explicitspecified at the top of the module? What error are you getting? "my script is not liking {line of code}" doesn't say much about the problem you're having.– Mathieu Guindon
Nov 16 '18 at 16:33