Adding a "blank" Line
EE Pros,
I have a nice macro that automatically adds a new line when "fired" based on a field name reference (Priority_Value_Case).
Here's the problem. If I have added data to the cells PRIOR to firing the macro, the macro automatically copies the range that has the added data. What I want is a new row that does not have the data I've put into the prior record / row. I think I can do this with a "clearcontents" command but don't know how for it to look at that added line only.
Here is the actual code:
'This code adds a new Case to the end of the last case.
'This adds a new case up to 12 cases
Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Application.DisplayAlerts = False
Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offs
If rngNewCase.Row > 19 Then 'To limit to A19 'comment out one of the two
'if rngnewcase.row - 6 > 10 then 'To limit to 10 rows 'comment out one of the two
MsgBox "you have exceeded the number of entries permitted"
Exit Sub
End If
Worksheets("Outcome_Value"
ChartNo = rngNewCase.Row / 3
Set rngCurrent = rngNewCase.Offset(, 2)
Set rngDesired = rngNewCase.Offset(, 3)
End Sub
Thank you in advance,
B.
I have a nice macro that automatically adds a new line when "fired" based on a field name reference (Priority_Value_Case).
Here's the problem. If I have added data to the cells PRIOR to firing the macro, the macro automatically copies the range that has the added data. What I want is a new row that does not have the data I've put into the prior record / row. I think I can do this with a "clearcontents" command but don't know how for it to look at that added line only.
Here is the actual code:
'This code adds a new Case to the end of the last case.
'This adds a new case up to 12 cases
Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Application.DisplayAlerts = False
Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offs
If rngNewCase.Row > 19 Then 'To limit to A19 'comment out one of the two
'if rngnewcase.row - 6 > 10 then 'To limit to 10 rows 'comment out one of the two
MsgBox "you have exceeded the number of entries permitted"
Exit Sub
End If
Worksheets("Outcome_Value"
ChartNo = rngNewCase.Row / 3
Set rngCurrent = rngNewCase.Offset(, 2)
Set rngDesired = rngNewCase.Offset(, 3)
End Sub
Thank you in advance,
B.
Martin Liss
Can you show by example the before and after and where you want the blank row?
ASKER
MartinLiss,
Thanks for asking! Let me take one try at explaining this; if you still need the example, I'll mock one up.
The Range name covers A10:H10 and simply copies it when the Macro is fired. If I have added data in the cells (A10:H10) then it copies all of it to the next row down. What I'm looking for is the one line of code that clears the data (not formatting) in what becomes A11:H11. Then I add data to the new line and when I fire it again, I want it to again, replicate the line and clear any of the data.
Here is the line where the copy is created:
Worksheets("Outcome_Value"
ChartNo = rngNewCase.Row / 3
Set rngCurrent = rngNewCase.Offset(, 2)
Set rngDesired = rngNewCase.Offset(, 3)
Do I need to mock up a WS?
B.
Thanks for asking! Let me take one try at explaining this; if you still need the example, I'll mock one up.
The Range name covers A10:H10 and simply copies it when the Macro is fired. If I have added data in the cells (A10:H10) then it copies all of it to the next row down. What I'm looking for is the one line of code that clears the data (not formatting) in what becomes A11:H11. Then I add data to the new line and when I fire it again, I want it to again, replicate the line and clear any of the data.
Here is the line where the copy is created:
Worksheets("Outcome_Value"
ChartNo = rngNewCase.Row / 3
Set rngCurrent = rngNewCase.Offset(, 2)
Set rngDesired = rngNewCase.Offset(, 3)
Do I need to mock up a WS?
B.
If it's not too much trouble.
ASKER
OK....here it is. You will see the line I'm trying to replicate.
Example-of-adding-a-Case.xlsm
Example-of-adding-a-Case.xlsm
Sorry but I'm still not exactly sure what you need. In any case I added a couple of buttons. My apologies if you already know how to do the things they do.
Example-of-adding-a-Case.xlsm
Example-of-adding-a-Case.xlsm
ASKER
MartinLiss,
Thank you! What I'm looking for is when you fire "Add Case" it simply replicates the line above until it hits a count of 15, then delivers the message in the code "You've exceeded the capacity of the model......".
Is that doable?
B.
Thank you! What I'm looking for is when you fire "Add Case" it simply replicates the line above until it hits a count of 15, then delivers the message in the code "You've exceeded the capacity of the model......".
Is that doable?
B.
Try this. I commented out some lines that you may need so be careful.
End Sub
Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Dim lngLastRow As Long
'ActiveSheet.Unprotect Password:="jam"
Application.DisplayAlerts = False
lngLastRow = Range("B65536").End(xlUp).Row
'Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(2)
' If rngNewCase.Row > 24 Then
If lngLastRow > 23 Then
MsgBox "you have exceeded the number of entries permitted"
Exit Sub
End If
Rows(lngLastRow & ":" & lngLastRow).Select
Selection.Copy
Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Select
ActiveSheet.Paste
'Worksheets("Outcome_Value").Range("Priority_Value_Case").Copy rngNewCase
' ChartNo = rngNewCase.Row / 3
' Set rngCurrent = rngNewCase.Offset(, 2)
' Set rngDesired = rngNewCase.Offset(, 3)
' ActiveSheet.Protect Password:="jam"
' ActiveSheet.EnableSelection = xlUnlockedCellsEnd Sub
ASKER
MartinLiss,
This works "fairly well". Only one issue. When I fire the macro, it does copy the appropriate line to the next line, but I get a hashlined (like a copy command) around the line it is copying. Is there a way of keeping that from showing up?
B.
This works "fairly well". Only one issue. When I fire the macro, it does copy the appropriate line to the next line, but I get a hashlined (like a copy command) around the line it is copying. Is there a way of keeping that from showing up?
B.
Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Dim lngLastRow As Long
'ActiveSheet.Unprotect Password:="jam"
Application.DisplayAlerts = False
lngLastRow = Range("B65536").End(xlUp).Row
'Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(2)
' If rngNewCase.Row > 24 Then
If lngLastRow > 23 Then
MsgBox "you have exceeded the number of entries permitted"
Exit Sub
End If
Application.ScreenUpdating = False
Rows(lngLastRow & ":" & lngLastRow).Select
Selection.Copy
Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.ScreenUpdating = True
'Worksheets("Outcome_Value").Range("Priority_Value_Case").Copy rngNewCase
' ChartNo = rngNewCase.Row / 3
' Set rngCurrent = rngNewCase.Offset(, 2)
' Set rngDesired = rngNewCase.Offset(, 3)
' ActiveSheet.Protect Password:="jam"
' ActiveSheet.EnableSelection = xlUnlockedCells
End SubASKER
MartinLiss,
Almost perfect! Here's the final problem; If I add a line, without any input, it works perfectly. If I fill out the responses in the last line, THEN add a line, it simply copies the line above, with the additions into the new line. I need the single line of macro code, that "clears" the input BEFORE it copies to the next line. Make sense?
B.
Almost perfect! Here's the final problem; If I add a line, without any input, it works perfectly. If I fill out the responses in the last line, THEN add a line, it simply copies the line above, with the additions into the new line. I need the single line of macro code, that "clears" the input BEFORE it copies to the next line. Make sense?
B.
ASKER
MartinLiss,
I think what I need is a line like;
.range (?????).ClearContents
that is between when the row is copied and when it actually pastes it or just after it pastes it. I just don't know how to describe the particular copied line in the range........
B.
I think what I need is a line like;
.range (?????).ClearContents
that is between when the row is copied and when it actually pastes it or just after it pastes it. I just don't know how to describe the particular copied line in the range........
B.
Hi Bright01
Do you still need help on this one ?
gowflow
Do you still need help on this one ?
gowflow
ASKER
Gowflow, thanks for touching base....not sure what happened to MartinLiss; hope he is all right. To answer your question, "yes" I do need this one little thing completed. Here's the issue. The macro I have that Martin worked on for me needs to be able to produce a identical line (copied), but without any of the data that is in the line. In other words, the code properly works; it's just that when you have put data in the first line and then go to add/replicate the line, it copies the line AND the data instead of just the line. I think all it needs is one line of code that "clears content" (perserving the drop downs, conditional formatting, etc. etc. -- just no data), in the newly created line.
Make sense? Do I need to send you the latest code or can you see it from Martin's 1/15 post above?
Thank you,
B.
Make sense? Do I need to send you the latest code or can you see it from Martin's 1/15 post above?
Thank you,
B.
no sweat will look at it once I touch base shortly.
gowflow
gowflow
Sorry for the dealy I was caught in other rushed issues.
I looked up your file and fixed the clear content but then noticed that for Col A you have a drop down that its values are picked from a list in Col T and every time we insert a line it messes the validation as they are in the way. So I moved the validation list onto a separate Validation sheet (that we can hide if you want but now left it for you to look at and make sure it is a complete list).
So try the Add Case and see if this is what your looking for.
gowflow
Example-of-adding-a-Case.xlsm
I looked up your file and fixed the clear content but then noticed that for Col A you have a drop down that its values are picked from a list in Col T and every time we insert a line it messes the validation as they are in the way. So I moved the validation list onto a separate Validation sheet (that we can hide if you want but now left it for you to look at and make sure it is a complete list).
So try the Add Case and see if this is what your looking for.
gowflow
Example-of-adding-a-Case.xlsm
not sure what happened to MartinLiss; hope he is all rightI'm still here. How can I help?
ASKER
MartinLiss and Gowflow,
I want to thank both of you for the help on this one. MartinLiss, when I didn't hear from you I added two questions to try to reengage. Gowflow reached out and asked if I still needed help. I'm glad to hear you are all right.
So, the file that Gowflow sent me is built on what you did MartinLiss but I need to be able to simply add a line with no data in it. Gowflow's fix does that but only adds one line. I need to add a new line each time I fire the macro.
Again, appreciate all of the help........ I'm certain either of you can finish this out but one of you may want to take it and declare it since both of you probably don't want to work on the same code.
B.
I want to thank both of you for the help on this one. MartinLiss, when I didn't hear from you I added two questions to try to reengage. Gowflow reached out and asked if I still needed help. I'm glad to hear you are all right.
So, the file that Gowflow sent me is built on what you did MartinLiss but I need to be able to simply add a line with no data in it. Gowflow's fix does that but only adds one line. I need to add a new line each time I fire the macro.
Again, appreciate all of the help........ I'm certain either of you can finish this out but one of you may want to take it and declare it since both of you probably don't want to work on the same code.
B.
Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Dim lngLastRow As Long
'ActiveSheet.Unprotect Password:="jam"
Application.DisplayAlerts = False
lngLastRow = Range("B65536").End(xlUp).
'Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offs
' If rngNewCase.Row > 24 Then
If lngLastRow > 23 Then
MsgBox "you have exceeded the number of entries permitted"
Exit Sub
End If
Application.ScreenUpdating
Rows(lngLastRow & ":" & lngLastRow).Select
Selection.Copy
Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Select
ActiveSheet.Paste
Rows(lngLastRow + 1 & ":" & lngLastRow + 1).ClearContents
'new
Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Value = "Strategy"
Application.CutCopyMode = False
Application.ScreenUpdating
'Worksheets("Outcome_Value
' ChartNo = rngNewCase.Row / 3
' Set rngCurrent = rngNewCase.Offset(, 2)
' Set rngDesired = rngNewCase.Offset(, 3)
' ActiveSheet.Protect Password:="jam"
' ActiveSheet.EnableSelectio
End Sub
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Dim lngLastRow As Long
'ActiveSheet.Unprotect Password:="jam"
Application.DisplayAlerts = False
lngLastRow = Range("B65536").End(xlUp).
'Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offs
' If rngNewCase.Row > 24 Then
If lngLastRow > 23 Then
MsgBox "you have exceeded the number of entries permitted"
Exit Sub
End If
Application.ScreenUpdating
Rows(lngLastRow & ":" & lngLastRow).Select
Selection.Copy
Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Select
ActiveSheet.Paste
Rows(lngLastRow + 1 & ":" & lngLastRow + 1).ClearContents
'new
Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Value = "Strategy"
Application.CutCopyMode = False
Application.ScreenUpdating
'Worksheets("Outcome_Value
' ChartNo = rngNewCase.Row / 3
' Set rngCurrent = rngNewCase.Offset(, 2)
' Set rngDesired = rngNewCase.Offset(, 3)
' ActiveSheet.Protect Password:="jam"
' ActiveSheet.EnableSelectio
End Sub
Sorry, that new line should be
Cells(lngLastRow + 1, 1).Value = "Strategy"
and not
Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Value = "Strategy"
Cells(lngLastRow + 1, 1).Value = "Strategy"
and not
Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Value = "Strategy"
ASKER
MartinLiss,
Thank you. I have added your code and can't get it to work. So what I've done is attached a new version with both macros. You will see + Add Old and + Add New buttons. The Old is the old code that almost works. The New is the new code you just sent me. In order to understand the problem,
1.) Save the file (you'll need to restore it after trying the + New macro)
2.) Press the + Old macro and you will see lines replicated below each line.
3.) Hit the erase button.
4.) Now put some text in cell B7 .... any thing.
5.) Now again, press the + Old macro and you will see that it copies B7 and the line with the text. That text should not be there....it should be a blank new line.
The new code has problems with some data that rotates the Activity numbers. It can be moved to anther set of cells and can be redirected in the associated macro.... you don't need to worry about that.
Hope that helps.
B.
Risk-Graphic-v5.xlsm
Thank you. I have added your code and can't get it to work. So what I've done is attached a new version with both macros. You will see + Add Old and + Add New buttons. The Old is the old code that almost works. The New is the new code you just sent me. In order to understand the problem,
1.) Save the file (you'll need to restore it after trying the + New macro)
2.) Press the + Old macro and you will see lines replicated below each line.
3.) Hit the erase button.
4.) Now put some text in cell B7 .... any thing.
5.) Now again, press the + Old macro and you will see that it copies B7 and the line with the text. That text should not be there....it should be a blank new line.
The new code has problems with some data that rotates the Activity numbers. It can be moved to anther set of cells and can be redirected in the associated macro.... you don't need to worry about that.
Hope that helps.
B.
Risk-Graphic-v5.xlsm
ASKER
OK....I changed the code with the new line and moved the data below. HOwever, still get an error in the +New Line. It should only replicate A7:D7 and not have any data content in the new line.
Risk-Graphic-v6.xlsm
Risk-Graphic-v6.xlsm
I copied goflow's workbook that he attached in post ID: 38818812. I then modified the code that I posted above. I'm attaching the result. What the code does is that it it adds 'blank' lines with "Strategy" in column A. The text is there so that the code can determine the last line. If putting that text there is a problem I could put some sort of indicator in some cell off-screen like "AA1" and use that to determine the last row.
Example-of-adding-a-Case-1.xlsm
Example-of-adding-a-Case-1.xlsm
I'll leave you in good hands I'm sure Martin will get you to shore safely.
gowflow
gowflow
ASKER
MartinLiss,
It isn't the right code and doesn't work. Look at the version I sent you. There is no word "Strategy". What I'm trying to do is simply create another record A:D as what happens when you click on + Line Item - Old. The problem is simple. When you only have 1 line, and you type in something in A:D, and then you click on +Line Item -Old, it copies the cells (A:D) and all the data in the first row. I'm trying to get it to copy the cells (with formatting, dropdown, etc.) WITHOUT the data copied.....so a fresh cell is presented.
Here is the latest copy.
Risk-Graphic-v7.xlsm
It isn't the right code and doesn't work. Look at the version I sent you. There is no word "Strategy". What I'm trying to do is simply create another record A:D as what happens when you click on + Line Item - Old. The problem is simple. When you only have 1 line, and you type in something in A:D, and then you click on +Line Item -Old, it copies the cells (A:D) and all the data in the first row. I'm trying to get it to copy the cells (with formatting, dropdown, etc.) WITHOUT the data copied.....so a fresh cell is presented.
Here is the latest copy.
Risk-Graphic-v7.xlsm
Sorry for the confusion. I'm away from my desk but should be able to get back to you in a few hours.
I don't see any dropdowns in columns A to D. anyhow try this
Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
If rngNewCase.Row > 19 Then 'To limit to A19 'comment out one of the two
'if rngnewcase.row - 6 > 10 then 'To limit to 10 rows 'comment out one of the two
MsgBox "you have exceeded the number of entries permitted"
Exit Sub
End If
Worksheets("Risk_Return").Range("Priority_Case").Copy rngNewCase
ChartNo = rngNewCase.Row / 3
Set rngCurrent = rngNewCase.Offset(, 2)
Set rngDesired = rngNewCase.Offset(, 3)
Range("A" & rngDesired.Row & ":" & "D" & rngDesired.Row).ClearContentsASKER
MartinLiss,
This is almost right! And you are correct...my mistake, there are no dropdowns -- but if there were, I would not want them "erased" in the copy/paste (i.e. still available on the new line).
The only flaw in this is that whenever the macro is fired, it needs to add a new line. Right now it only adds one more new line.
I did add text, and you have solved the problem of not copying the text to the new line. Now if we can add a line each time the macro is fired, that would be perfect!
Thank you for hanging in with me on this one.
B.
This is almost right! And you are correct...my mistake, there are no dropdowns -- but if there were, I would not want them "erased" in the copy/paste (i.e. still available on the new line).
The only flaw in this is that whenever the macro is fired, it needs to add a new line. Right now it only adds one more new line.
I did add text, and you have solved the problem of not copying the text to the new line. Now if we can add a line each time the macro is fired, that would be perfect!
Thank you for hanging in with me on this one.
B.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! I thought this was going to be easy; thank you very much for sticking with me on this.... I can now proceed with my model. Again, really appreciate the code and the lesson.
Just curious, how could I have made this easier for you? One of the great things about EE is that you have to learn how to communicate and interpret the requirements via email/text. It makes both the source and the provider better communitors. I would like to get better at explaining what I need.
Thanks again!
B.
Just curious, how could I have made this easier for you? One of the great things about EE is that you have to learn how to communicate and interpret the requirements via email/text. It makes both the source and the provider better communitors. I would like to get better at explaining what I need.
Thanks again!
B.
ASKER
MartinLiss,
Your code works great! I'm going to open an additional question with EE to fix the "Prioritize" macro. The problem is simple; and I missed it until I tested it. Here's the issue; when a new line is added, the first cell needs to have a "1" in it. If it doesn't, I get an error code (400) when I fire the "prioritize" macro. I hope you will pick this one up; no one knows this code better.
Much thanks,
B.
Your code works great! I'm going to open an additional question with EE to fix the "Prioritize" macro. The problem is simple; and I missed it until I tested it. Here's the issue; when a new line is added, the first cell needs to have a "1" in it. If it doesn't, I get an error code (400) when I fire the "prioritize" macro. I hope you will pick this one up; no one knows this code better.
Much thanks,
B.
You're welcome and I'm glad I was able to help. BTW I would have given you the answer to your new question for free:) And as far as explaining what you need, one thing you could have done do to make it easier for us was to say something like the following. Note that the addresses are guessed at for this post.
In other words be very specific about the results you want to see.
Marty - MVP 2009 to 2012
Here's what I need to have happen. In the attached workbook when I click the <caption of the button> command button, I want a row that is completely blank to appear in Cells A11 to D11, and if clicked again in cells A12 to D12. I also want to limit the number of times the button can be clicked so that row 25 is the last possible line.
In other words be very specific about the results you want to see.
Marty - MVP 2009 to 2012