Check a range of cells for a value, and then clear contents after making a new sheet
I've been lurking on StackOverflow for a little while and I've cobbled together some code to help automate generation of a new daily report sheet. My code runs fine for finding previous report, generating a new report with +1 and clearing some ranges to need reset every day.
My current issue is trying to clear some conditional cells. I've tried several close use cases here and other excel/VBA sites but I'm not proficient enough with VBA to be able to translate directly into my code's verbage.
To be more specific, for all rows 47 to 79 I need to check IF merged cell H47:I47 contains the value "N" for a simple yes no check
If this condition is true THEN I need to .clearcontents the columns to the left of the check cell. so A47:G47. I then need it to repeat this check for each row down to 79.
I think my issue is being over complicated by the current sheet naming structure and trying to reference the ever changing "sht_NewWeek)
I'll paste what I have so far below and look forward to your help
Sub CreateNewSheet()
Dim wrkSht As Worksheet
Dim lWkNum As Long
Dim lCurNum As Long
Dim sht_LastWeek As Worksheet
Dim sht_NewWeek As Worksheet
'Find previous week and set reference to it.
For Each wrkSht In ThisWorkbook.Worksheets
If IsNumeric(Replace(wrkSht.Name, "Report ", "")) Then
lCurNum = CLng(Replace(wrkSht.Name, "Report ", ""))
If lCurNum > lWkNum Then lWkNum = lCurNum
End If
Next wrkSht
Set sht_LastWeek = ThisWorkbook.Worksheets("Report " & lWkNum)
'Create new sheet, set reference to it and rename.
sht_LastWeek.Copy after:=Sheets(sht_LastWeek.Index)
Set sht_NewWeek = Sheets(sht_LastWeek.Index + 1)
sht_NewWeek.Name = "Report " & lCurNum + 1
'Clear the cells and relink formula to previous sheet, while updating report date to today
With sht_NewWeek
.Range("D16:M16,B20:B44,C20:C44,D20:M44,D19:M19").ClearContents
.Range("F12").Value = "=Today()"
.Cells.Replace What:="'Report " & lCurNum - 1 & "'!", _
Replacement:="'Report " & lCurNum & "'!", _
LookAt:=xlPart
End With
End Sub
excel vba excel-vba
edited Nov 21 '18 at 19:43
BigBen
6,0192418
asked Nov 21 '18 at 19:42
jtlogcjtlogc
1
add a comment |
I've been lurking on StackOverflow for a little while and I've cobbled together some code to help automate generation of a new daily report sheet. My code runs fine for finding previous report, generating a new report with +1 and clearing some ranges to need reset every day.
My current issue is trying to clear some conditional cells. I've tried several close use cases here and other excel/VBA sites but I'm not proficient enough with VBA to be able to translate directly into my code's verbage.
To be more specific, for all rows 47 to 79 I need to check IF merged cell H47:I47 contains the value "N" for a simple yes no check
If this condition is true THEN I need to .clearcontents the columns to the left of the check cell. so A47:G47. I then need it to repeat this check for each row down to 79.
I think my issue is being over complicated by the current sheet naming structure and trying to reference the ever changing "sht_NewWeek)
I'll paste what I have so far below and look forward to your help
Sub CreateNewSheet()
Dim wrkSht As Worksheet
Dim lWkNum As Long
Dim lCurNum As Long
Dim sht_LastWeek As Worksheet
Dim sht_NewWeek As Worksheet
'Find previous week and set reference to it.
For Each wrkSht In ThisWorkbook.Worksheets
If IsNumeric(Replace(wrkSht.Name, "Report ", "")) Then
lCurNum = CLng(Replace(wrkSht.Name, "Report ", ""))
If lCurNum > lWkNum Then lWkNum = lCurNum
End If
Next wrkSht
Set sht_LastWeek = ThisWorkbook.Worksheets("Report " & lWkNum)
'Create new sheet, set reference to it and rename.
sht_LastWeek.Copy after:=Sheets(sht_LastWeek.Index)
Set sht_NewWeek = Sheets(sht_LastWeek.Index + 1)
sht_NewWeek.Name = "Report " & lCurNum + 1
'Clear the cells and relink formula to previous sheet, while updating report date to today
With sht_NewWeek
.Range("D16:M16,B20:B44,C20:C44,D20:M44,D19:M19").ClearContents
.Range("F12").Value = "=Today()"
.Cells.Replace What:="'Report " & lCurNum - 1 & "'!", _
Replacement:="'Report " & lCurNum & "'!", _
LookAt:=xlPart
End With
End Sub
excel vba excel-vba
edited Nov 21 '18 at 19:43
BigBen
6,0192418
asked Nov 21 '18 at 19:42
jtlogcjtlogc
1
1
In your sheet test, you are comparinglCurlNumtolwkNumbut notice you have not assigned a value tolwkNum. This meanslwkNumis defaulting to 0. Your test can be reduced toIf lCurNum > 0 Then 0 = lCurNum. Before you enter the worksheet loop, you need to assign a value tolwkNum
– urdearboy
Nov 21 '18 at 19:49
Thanks for the insight. And sorry for not stating the current status of my code. Right now the code, when ran, successfully takes the sheet I’m in named “Report X”, copies it, renames it “Report X+1” and clear the relevant cells of the report that need reset. It also changes the report day to today’s date. Every as written currently runs without error. I’m trying append a whole new function onto the end to run an additional task as stated in my OP. The syntax of using “weeks” for sheet names is an artifact of the code i utilized from another post with a similar goal of naming new sheets.
– jtlogc
Nov 22 '18 at 20:23
add a comment |
I've been lurking on StackOverflow for a little while and I've cobbled together some code to help automate generation of a new daily report sheet. My code runs fine for finding previous report, generating a new report with +1 and clearing some ranges to need reset every day.
My current issue is trying to clear some conditional cells. I've tried several close use cases here and other excel/VBA sites but I'm not proficient enough with VBA to be able to translate directly into my code's verbage.
To be more specific, for all rows 47 to 79 I need to check IF merged cell H47:I47 contains the value "N" for a simple yes no check
If this condition is true THEN I need to .clearcontents the columns to the left of the check cell. so A47:G47. I then need it to repeat this check for each row down to 79.
I think my issue is being over complicated by the current sheet naming structure and trying to reference the ever changing "sht_NewWeek)
I'll paste what I have so far below and look forward to your help
Sub CreateNewSheet()
Dim wrkSht As Worksheet
Dim lWkNum As Long
Dim lCurNum As Long
Dim sht_LastWeek As Worksheet
Dim sht_NewWeek As Worksheet
'Find previous week and set reference to it.
For Each wrkSht In ThisWorkbook.Worksheets
If IsNumeric(Replace(wrkSht.Name, "Report ", "")) Then
lCurNum = CLng(Replace(wrkSht.Name, "Report ", ""))
If lCurNum > lWkNum Then lWkNum = lCurNum
End If
Next wrkSht
Set sht_LastWeek = ThisWorkbook.Worksheets("Report " & lWkNum)
'Create new sheet, set reference to it and rename.
sht_LastWeek.Copy after:=Sheets(sht_LastWeek.Index)
Set sht_NewWeek = Sheets(sht_LastWeek.Index + 1)
sht_NewWeek.Name = "Report " & lCurNum + 1
'Clear the cells and relink formula to previous sheet, while updating report date to today
With sht_NewWeek
.Range("D16:M16,B20:B44,C20:C44,D20:M44,D19:M19").ClearContents
.Range("F12").Value = "=Today()"
.Cells.Replace What:="'Report " & lCurNum - 1 & "'!", _
Replacement:="'Report " & lCurNum & "'!", _
LookAt:=xlPart
End With
End Sub
excel vba excel-vba
edited Nov 21 '18 at 19:43
BigBen
6,0192418
asked Nov 21 '18 at 19:42
jtlogcjtlogc
1
I've been lurking on StackOverflow for a little while and I've cobbled together some code to help automate generation of a new daily report sheet. My code runs fine for finding previous report, generating a new report with +1 and clearing some ranges to need reset every day.
My current issue is trying to clear some conditional cells. I've tried several close use cases here and other excel/VBA sites but I'm not proficient enough with VBA to be able to translate directly into my code's verbage.
To be more specific, for all rows 47 to 79 I need to check IF merged cell H47:I47 contains the value "N" for a simple yes no check
If this condition is true THEN I need to .clearcontents the columns to the left of the check cell. so A47:G47. I then need it to repeat this check for each row down to 79.
I think my issue is being over complicated by the current sheet naming structure and trying to reference the ever changing "sht_NewWeek)
I'll paste what I have so far below and look forward to your help
Sub CreateNewSheet()
Dim wrkSht As Worksheet
Dim lWkNum As Long
Dim lCurNum As Long
Dim sht_LastWeek As Worksheet
Dim sht_NewWeek As Worksheet
'Find previous week and set reference to it.
For Each wrkSht In ThisWorkbook.Worksheets
If IsNumeric(Replace(wrkSht.Name, "Report ", "")) Then
lCurNum = CLng(Replace(wrkSht.Name, "Report ", ""))
If lCurNum > lWkNum Then lWkNum = lCurNum
End If
Next wrkSht
Set sht_LastWeek = ThisWorkbook.Worksheets("Report " & lWkNum)
'Create new sheet, set reference to it and rename.
sht_LastWeek.Copy after:=Sheets(sht_LastWeek.Index)
Set sht_NewWeek = Sheets(sht_LastWeek.Index + 1)
sht_NewWeek.Name = "Report " & lCurNum + 1
'Clear the cells and relink formula to previous sheet, while updating report date to today
With sht_NewWeek
.Range("D16:M16,B20:B44,C20:C44,D20:M44,D19:M19").ClearContents
.Range("F12").Value = "=Today()"
.Cells.Replace What:="'Report " & lCurNum - 1 & "'!", _
Replacement:="'Report " & lCurNum & "'!", _
LookAt:=xlPart
End With
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 21 '18 at 19:43
BigBen
6,0192418
asked Nov 21 '18 at 19:42
jtlogcjtlogc
1
edited Nov 21 '18 at 19:43
BigBen
6,0192418
asked Nov 21 '18 at 19:42
jtlogcjtlogc
1
edited Nov 21 '18 at 19:43
BigBen
6,0192418
edited Nov 21 '18 at 19:43
BigBen
6,0192418
edited Nov 21 '18 at 19:43
BigBen
6,0192418
6,0192418
asked Nov 21 '18 at 19:42
jtlogcjtlogc
1
asked Nov 21 '18 at 19:42
jtlogcjtlogc
1
asked Nov 21 '18 at 19:42
jtlogcjtlogc
1
1
1
In your sheet test, you are comparinglCurlNumtolwkNumbut notice you have not assigned a value tolwkNum. This meanslwkNumis defaulting to 0. Your test can be reduced toIf lCurNum > 0 Then 0 = lCurNum. Before you enter the worksheet loop, you need to assign a value tolwkNum
– urdearboy
Nov 21 '18 at 19:49
Thanks for the insight. And sorry for not stating the current status of my code. Right now the code, when ran, successfully takes the sheet I’m in named “Report X”, copies it, renames it “Report X+1” and clear the relevant cells of the report that need reset. It also changes the report day to today’s date. Every as written currently runs without error. I’m trying append a whole new function onto the end to run an additional task as stated in my OP. The syntax of using “weeks” for sheet names is an artifact of the code i utilized from another post with a similar goal of naming new sheets.
– jtlogc
Nov 22 '18 at 20:23
add a comment |
1
In your sheet test, you are comparinglCurlNumtolwkNumbut notice you have not assigned a value tolwkNum. This meanslwkNumis defaulting to 0. Your test can be reduced toIf lCurNum > 0 Then 0 = lCurNum. Before you enter the worksheet loop, you need to assign a value tolwkNum
– urdearboy
Nov 21 '18 at 19:49
Thanks for the insight. And sorry for not stating the current status of my code. Right now the code, when ran, successfully takes the sheet I’m in named “Report X”, copies it, renames it “Report X+1” and clear the relevant cells of the report that need reset. It also changes the report day to today’s date. Every as written currently runs without error. I’m trying append a whole new function onto the end to run an additional task as stated in my OP. The syntax of using “weeks” for sheet names is an artifact of the code i utilized from another post with a similar goal of naming new sheets.
– jtlogc
Nov 22 '18 at 20:23
1
1
In your sheet test, you are comparing
lCurlNum to lwkNum but notice you have not assigned a value to lwkNum. This means lwkNum is defaulting to 0. Your test can be reduced to If lCurNum > 0 Then 0 = lCurNum. Before you enter the worksheet loop, you need to assign a value to lwkNum– urdearboy
Nov 21 '18 at 19:49
In your sheet test, you are comparing
lCurlNum to lwkNum but notice you have not assigned a value to lwkNum. This means lwkNum is defaulting to 0. Your test can be reduced to If lCurNum > 0 Then 0 = lCurNum. Before you enter the worksheet loop, you need to assign a value to lwkNum– urdearboy
Nov 21 '18 at 19:49
Thanks for the insight. And sorry for not stating the current status of my code. Right now the code, when ran, successfully takes the sheet I’m in named “Report X”, copies it, renames it “Report X+1” and clear the relevant cells of the report that need reset. It also changes the report day to today’s date. Every as written currently runs without error. I’m trying append a whole new function onto the end to run an additional task as stated in my OP. The syntax of using “weeks” for sheet names is an artifact of the code i utilized from another post with a similar goal of naming new sheets.
– jtlogc
Nov 22 '18 at 20:23
Thanks for the insight. And sorry for not stating the current status of my code. Right now the code, when ran, successfully takes the sheet I’m in named “Report X”, copies it, renames it “Report X+1” and clear the relevant cells of the report that need reset. It also changes the report day to today’s date. Every as written currently runs without error. I’m trying append a whole new function onto the end to run an additional task as stated in my OP. The syntax of using “weeks” for sheet names is an artifact of the code i utilized from another post with a similar goal of naming new sheets.
– jtlogc
Nov 22 '18 at 20:23
add a comment |
0
active
oldest
votes
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%2f53419449%2fcheck-a-range-of-cells-for-a-value-and-then-clear-contents-after-making-a-new-s%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53419449%2fcheck-a-range-of-cells-for-a-value-and-then-clear-contents-after-making-a-new-s%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
1
In your sheet test, you are comparing
lCurlNumtolwkNumbut notice you have not assigned a value tolwkNum. This meanslwkNumis defaulting to 0. Your test can be reduced toIf lCurNum > 0 Then 0 = lCurNum. Before you enter the worksheet loop, you need to assign a value tolwkNum– urdearboy
Nov 21 '18 at 19:49
Thanks for the insight. And sorry for not stating the current status of my code. Right now the code, when ran, successfully takes the sheet I’m in named “Report X”, copies it, renames it “Report X+1” and clear the relevant cells of the report that need reset. It also changes the report day to today’s date. Every as written currently runs without error. I’m trying append a whole new function onto the end to run an additional task as stated in my OP. The syntax of using “weeks” for sheet names is an artifact of the code i utilized from another post with a similar goal of naming new sheets.
– jtlogc
Nov 22 '18 at 20:23