How do I insert month/year on multiple sheets?
I want to add the month and year for the previous month in A2 of multiple sheets without selecting each individual sheet. I'm setting up a template to run reports every month.
I've searched, but I can't locate examples that are similar and loop instead of selecting each sheet.
Sub dateins()
' Date as Mo and year. Current month minus one.
Dim dash As Worksheet, daily As Worksheet, mon As Worksheet
Dim Thisday As Date, Rptmon As Date, ws As Worksheet
Set dash = Sheets("Dashboard")
Set daily = Sheets("Daily")
Set mon = Sheets("Monthly")
Thisday = Date
Rptmon = DateAdd("m", -1, Thisday)
Rptmon = Format(Thisday, "mmmm yyyy")
For Each ws In ThisWorkbook.Sheets
Select Case ws.Name
Case "dash", "daily", "mon"
If ws.Range("A2") = "" Then
ws.Range("A2").Replace What:="", Replacement:=Rptmon
End If
End Select
Next ws
End Sub
This loop does not insert the date on any of the sheets. Thank you for your help!
excel vba
asked Jan 19 at 20:58
RebaSRebaS
162
add a comment |
I want to add the month and year for the previous month in A2 of multiple sheets without selecting each individual sheet. I'm setting up a template to run reports every month.
I've searched, but I can't locate examples that are similar and loop instead of selecting each sheet.
Sub dateins()
' Date as Mo and year. Current month minus one.
Dim dash As Worksheet, daily As Worksheet, mon As Worksheet
Dim Thisday As Date, Rptmon As Date, ws As Worksheet
Set dash = Sheets("Dashboard")
Set daily = Sheets("Daily")
Set mon = Sheets("Monthly")
Thisday = Date
Rptmon = DateAdd("m", -1, Thisday)
Rptmon = Format(Thisday, "mmmm yyyy")
For Each ws In ThisWorkbook.Sheets
Select Case ws.Name
Case "dash", "daily", "mon"
If ws.Range("A2") = "" Then
ws.Range("A2").Replace What:="", Replacement:=Rptmon
End If
End Select
Next ws
End Sub
This loop does not insert the date on any of the sheets. Thank you for your help!
excel vba
asked Jan 19 at 20:58
RebaSRebaS
162
What are the full case-sensitive names of the worksheets you want to apply the date to?
– user10931127
Jan 19 at 21:02
"Dashboard"," Daily" ,"Monthly"
– RebaS
Jan 19 at 21:05
I changed to the full case-sensitive names and it worked beautifully! Thank you for the simple correction.
– RebaS
Jan 19 at 21:09
add a comment |
I want to add the month and year for the previous month in A2 of multiple sheets without selecting each individual sheet. I'm setting up a template to run reports every month.
I've searched, but I can't locate examples that are similar and loop instead of selecting each sheet.
Sub dateins()
' Date as Mo and year. Current month minus one.
Dim dash As Worksheet, daily As Worksheet, mon As Worksheet
Dim Thisday As Date, Rptmon As Date, ws As Worksheet
Set dash = Sheets("Dashboard")
Set daily = Sheets("Daily")
Set mon = Sheets("Monthly")
Thisday = Date
Rptmon = DateAdd("m", -1, Thisday)
Rptmon = Format(Thisday, "mmmm yyyy")
For Each ws In ThisWorkbook.Sheets
Select Case ws.Name
Case "dash", "daily", "mon"
If ws.Range("A2") = "" Then
ws.Range("A2").Replace What:="", Replacement:=Rptmon
End If
End Select
Next ws
End Sub
This loop does not insert the date on any of the sheets. Thank you for your help!
excel vba
asked Jan 19 at 20:58
RebaSRebaS
162
I want to add the month and year for the previous month in A2 of multiple sheets without selecting each individual sheet. I'm setting up a template to run reports every month.
I've searched, but I can't locate examples that are similar and loop instead of selecting each sheet.
Sub dateins()
' Date as Mo and year. Current month minus one.
Dim dash As Worksheet, daily As Worksheet, mon As Worksheet
Dim Thisday As Date, Rptmon As Date, ws As Worksheet
Set dash = Sheets("Dashboard")
Set daily = Sheets("Daily")
Set mon = Sheets("Monthly")
Thisday = Date
Rptmon = DateAdd("m", -1, Thisday)
Rptmon = Format(Thisday, "mmmm yyyy")
For Each ws In ThisWorkbook.Sheets
Select Case ws.Name
Case "dash", "daily", "mon"
If ws.Range("A2") = "" Then
ws.Range("A2").Replace What:="", Replacement:=Rptmon
End If
End Select
Next ws
End Sub
This loop does not insert the date on any of the sheets. Thank you for your help!
excel vba
excel vba
asked Jan 19 at 20:58
RebaSRebaS
162
asked Jan 19 at 20:58
RebaSRebaS
162
asked Jan 19 at 20:58
RebaSRebaS
162
asked Jan 19 at 20:58
RebaSRebaS
162
asked Jan 19 at 20:58
RebaSRebaS
162
162
What are the full case-sensitive names of the worksheets you want to apply the date to?
– user10931127
Jan 19 at 21:02
"Dashboard"," Daily" ,"Monthly"
– RebaS
Jan 19 at 21:05
I changed to the full case-sensitive names and it worked beautifully! Thank you for the simple correction.
– RebaS
Jan 19 at 21:09
add a comment |
What are the full case-sensitive names of the worksheets you want to apply the date to?
– user10931127
Jan 19 at 21:02
"Dashboard"," Daily" ,"Monthly"
– RebaS
Jan 19 at 21:05
I changed to the full case-sensitive names and it worked beautifully! Thank you for the simple correction.
– RebaS
Jan 19 at 21:09
What are the full case-sensitive names of the worksheets you want to apply the date to?
– user10931127
Jan 19 at 21:02
What are the full case-sensitive names of the worksheets you want to apply the date to?
– user10931127
Jan 19 at 21:02
"Dashboard"," Daily" ,"Monthly"
– RebaS
Jan 19 at 21:05
"Dashboard"," Daily" ,"Monthly"
– RebaS
Jan 19 at 21:05
I changed to the full case-sensitive names and it worked beautifully! Thank you for the simple correction.
– RebaS
Jan 19 at 21:09
I changed to the full case-sensitive names and it worked beautifully! Thank you for the simple correction.
– RebaS
Jan 19 at 21:09
add a comment |
1 Answer
1
active
oldest
votes
Your Select Case is looking at the worksheet names, not the variables you've assigned to the worksheet objects.
Sub dateins()
' Date as Mo and year. Current month minus one.
Dim dash As Worksheet, daily As Worksheet, mon As Worksheet
Dim Thisday As Date, Rptmon As Date, ws As Worksheet
Set dash = Sheets("Dashboard")
Set daily = Sheets("Daily")
Set mon = Sheets("Monthly")
Thisday = Date
Rptmon = DateAdd("m", -1, Thisday)
For Each ws In ThisWorkbook.Sheets
Select Case ws.Name
Case "Dashboard", "Daily", "Monthly"
If ws.Range("A2") = "" Then
ws.Range("A2") = Rptmon
ws.Range("A2").numberformat = "mmmm yyyy"
End If
End Select
Next ws
End Sub
You want to assign a true date to A2 and let the cell format display the "mmmm yyyy".
answered Jan 19 at 21:06
user10931127user10931127
993
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%2f54271337%2fhow-do-i-insert-month-year-on-multiple-sheets%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
Your Select Case is looking at the worksheet names, not the variables you've assigned to the worksheet objects.
Sub dateins()
' Date as Mo and year. Current month minus one.
Dim dash As Worksheet, daily As Worksheet, mon As Worksheet
Dim Thisday As Date, Rptmon As Date, ws As Worksheet
Set dash = Sheets("Dashboard")
Set daily = Sheets("Daily")
Set mon = Sheets("Monthly")
Thisday = Date
Rptmon = DateAdd("m", -1, Thisday)
For Each ws In ThisWorkbook.Sheets
Select Case ws.Name
Case "Dashboard", "Daily", "Monthly"
If ws.Range("A2") = "" Then
ws.Range("A2") = Rptmon
ws.Range("A2").numberformat = "mmmm yyyy"
End If
End Select
Next ws
End Sub
You want to assign a true date to A2 and let the cell format display the "mmmm yyyy".
answered Jan 19 at 21:06
user10931127user10931127
993
add a comment |
Your Select Case is looking at the worksheet names, not the variables you've assigned to the worksheet objects.
Sub dateins()
' Date as Mo and year. Current month minus one.
Dim dash As Worksheet, daily As Worksheet, mon As Worksheet
Dim Thisday As Date, Rptmon As Date, ws As Worksheet
Set dash = Sheets("Dashboard")
Set daily = Sheets("Daily")
Set mon = Sheets("Monthly")
Thisday = Date
Rptmon = DateAdd("m", -1, Thisday)
For Each ws In ThisWorkbook.Sheets
Select Case ws.Name
Case "Dashboard", "Daily", "Monthly"
If ws.Range("A2") = "" Then
ws.Range("A2") = Rptmon
ws.Range("A2").numberformat = "mmmm yyyy"
End If
End Select
Next ws
End Sub
You want to assign a true date to A2 and let the cell format display the "mmmm yyyy".
answered Jan 19 at 21:06
user10931127user10931127
993
add a comment |
Your Select Case is looking at the worksheet names, not the variables you've assigned to the worksheet objects.
Sub dateins()
' Date as Mo and year. Current month minus one.
Dim dash As Worksheet, daily As Worksheet, mon As Worksheet
Dim Thisday As Date, Rptmon As Date, ws As Worksheet
Set dash = Sheets("Dashboard")
Set daily = Sheets("Daily")
Set mon = Sheets("Monthly")
Thisday = Date
Rptmon = DateAdd("m", -1, Thisday)
For Each ws In ThisWorkbook.Sheets
Select Case ws.Name
Case "Dashboard", "Daily", "Monthly"
If ws.Range("A2") = "" Then
ws.Range("A2") = Rptmon
ws.Range("A2").numberformat = "mmmm yyyy"
End If
End Select
Next ws
End Sub
You want to assign a true date to A2 and let the cell format display the "mmmm yyyy".
answered Jan 19 at 21:06
user10931127user10931127
993
Your Select Case is looking at the worksheet names, not the variables you've assigned to the worksheet objects.
Sub dateins()
' Date as Mo and year. Current month minus one.
Dim dash As Worksheet, daily As Worksheet, mon As Worksheet
Dim Thisday As Date, Rptmon As Date, ws As Worksheet
Set dash = Sheets("Dashboard")
Set daily = Sheets("Daily")
Set mon = Sheets("Monthly")
Thisday = Date
Rptmon = DateAdd("m", -1, Thisday)
For Each ws In ThisWorkbook.Sheets
Select Case ws.Name
Case "Dashboard", "Daily", "Monthly"
If ws.Range("A2") = "" Then
ws.Range("A2") = Rptmon
ws.Range("A2").numberformat = "mmmm yyyy"
End If
End Select
Next ws
End Sub
You want to assign a true date to A2 and let the cell format display the "mmmm yyyy".
answered Jan 19 at 21:06
user10931127user10931127
993
answered Jan 19 at 21:06
user10931127user10931127
993
answered Jan 19 at 21:06
user10931127user10931127
993
answered Jan 19 at 21:06
user10931127user10931127
993
993
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.
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%2f54271337%2fhow-do-i-insert-month-year-on-multiple-sheets%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
What are the full case-sensitive names of the worksheets you want to apply the date to?
– user10931127
Jan 19 at 21:02
"Dashboard"," Daily" ,"Monthly"
– RebaS
Jan 19 at 21:05
I changed to the full case-sensitive names and it worked beautifully! Thank you for the simple correction.
– RebaS
Jan 19 at 21:09