Macro to insert row in protected sheet
Hi, I created easy macro below to enter a predesigned (formats and formulas)
row in a protected sheet and then protect it again. Now I would like to set the macro that it will insert the line above the cell in which I'm standing at the time that I activate the Macro. Furthermore, I would like to fix the row that I enter.... in this case it is row 65..but when I execute the Macro ones, it will become row 66... Can somebody tell me which changes I have to make in the codes?Thanks ActiveSheet.Unprotect ActiveWindow.SmallScroll Down:=21 Rows("65:65").Select Selection.Copy ActiveWindow.SmallScroll Down:=-21 Rows("20:20").Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub |
Macro to insert row in protected sheet
On Sep 24, 10:29*pm, Tom82 wrote:
Hi, I created easy macro below to enter a predesigned (formats and formulas) row in a protected sheet and then protect it again. Now I would like to set the macro that it will insert the line above the cell in which I'm standing at the time that I activate the Macro. Furthermore, I would like to fix the row that I enter.... in this case it is row 65..but when I execute the Macro ones, it will become row 66... Can somebody tell me which changes I have to make in the codes?Thanks ActiveSheet.Unprotect * * ActiveWindow.SmallScroll Down:=21 * * Rows("65:65").Select * * Selection.Copy * * ActiveWindow.SmallScroll Down:=-21 * * Rows("20:20").Select * * Selection.Insert Shift:=xlDown * * Application.CutCopyMode = False * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hello Tom, I assume what you want is to keep the row you have selected as the 'active' row, to return to after you have inserted a row in the range above. You use Cut/Copy - do you want the selected row cut and inserted somewhere else, or were you just using that as a method to insert a row? The following code is untested (can't run it on my Mac at home) but should insert a row before the currently selected cell, then select the row again. Steven Sub InsRw() Dim Rng as Range Dim Sht as Worksheet Set Sht = ActiveSheet Set Rng = ActiveCell.EntireRow Sht.Unprotect Rng.Insert Shift:=xlDown Set Rng = Rng.Offset(1,0) Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub |
Macro to insert row in protected sheet
Steven,
Thanks for your tip. The fact that it inserts a row above the selected cell is very usefull, but there is still something else I need. I want to insert not just a white row, but a row with specific formulas that I already created at the bottom of the sheet (e.g. on row 100). So it has to insert row 100 above the selected cell. If the macro is executed once, this means that the next time it should insert row 101 as it shifted one row down due the insertion. Is this possible? Thanks " wrote: On Sep 24, 10:29 pm, Tom82 wrote: Hi, I created easy macro below to enter a predesigned (formats and formulas) row in a protected sheet and then protect it again. Now I would like to set the macro that it will insert the line above the cell in which I'm standing at the time that I activate the Macro. Furthermore, I would like to fix the row that I enter.... in this case it is row 65..but when I execute the Macro ones, it will become row 66... Can somebody tell me which changes I have to make in the codes?Thanks ActiveSheet.Unprotect ActiveWindow.SmallScroll Down:=21 Rows("65:65").Select Selection.Copy ActiveWindow.SmallScroll Down:=-21 Rows("20:20").Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hello Tom, I assume what you want is to keep the row you have selected as the 'active' row, to return to after you have inserted a row in the range above. You use Cut/Copy - do you want the selected row cut and inserted somewhere else, or were you just using that as a method to insert a row? The following code is untested (can't run it on my Mac at home) but should insert a row before the currently selected cell, then select the row again. Steven Sub InsRw() Dim Rng as Range Dim Sht as Worksheet Set Sht = ActiveSheet Set Rng = ActiveCell.EntireRow Sht.Unprotect Rng.Insert Shift:=xlDown Set Rng = Rng.Offset(1,0) Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub |
Macro to insert row in protected sheet
Tom,
Is the source row the last row of data in the sheet? Will it always be the last row? It is possible if that is the case. Alternatively, you could put it above the header row, into Row 1 and hide the row. Or is there some unique identifier? Are the formulas the same in every row, because it could be copied from anywhere if that is the case. The following assumes the formula rows is always the last row. Steven Sub InsRw() Dim Rng as Range Dim Sht as Worksheet Dim SrcRow as Range Set Sht = ActiveSheet Set Rng = ActiveCell.EntireRow Set SrcRow = Range("A" & Sht.Cells(Rows.Count, "A").End(xlUp).Row).EntireRow Sht.Unprotect Rng.Insert Shift:=xlDown SrcRow.Copy Rng.Offset(-1, 0) Set Rng = Rng.Offset(1, 0) Rng.Select Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub On Sep 25, 11:55*am, Tom82 wrote: Steven, Thanks for your tip. The fact that it inserts a row above the selected cell is very usefull, but there is still something else I need. I want to insert not just a white row, but a row with specific formulas that I already created at the bottom of the sheet (e.g. on row 100). So it has to insert row 100 above the selected cell. If the macro is executed once, this means that the next time it should insert row 101 as it shifted one row down due the insertion. Is this possible? Thanks " wrote: On Sep 24, 10:29 pm, Tom82 wrote: Hi, I created easy macro below to enter a predesigned (formats and formulas) row in a protected sheet and then protect it again. Now I would like to set the macro that it will insert the line above the cell in which I'm standing at the time that I activate the Macro. Furthermore, I would like to fix the row that I enter.... in this case it is row 65..but when I execute the Macro ones, it will become row 66... Can somebody tell me which changes I have to make in the codes?Thanks ActiveSheet.Unprotect * * ActiveWindow.SmallScroll Down:=21 * * Rows("65:65").Select * * Selection.Copy * * ActiveWindow.SmallScroll Down:=-21 * * Rows("20:20").Select * * Selection.Insert Shift:=xlDown * * Application.CutCopyMode = False * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hello Tom, I assume what you want is to keep the row you have selected as the 'active' row, to return to after you have inserted a row in the range above. You use Cut/Copy - do you want the selected row cut and inserted somewhere else, or were you just using that as a method to insert a row? The following code is untested (can't run it on my Mac at home) but should insert a row before the currently selected cell, then select the row again. Steven Sub InsRw() Dim Rng as Range Dim Sht as Worksheet Set Sht = ActiveSheet Set Rng = ActiveCell.EntireRow Sht.Unprotect Rng.Insert Shift:=xlDown Set Rng = Rng.Offset(1,0) Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub |
Macro to insert row in protected sheet
Steven,
I tried to put the source row on the last row of the sheet, but when I execute the Macro, it refuses because the source row will be shifted of the sheet (because I enter an additional row somewhere else on the sheet) The formulas are indeed the same for every row. So, in theory I could copy the formulas from any other fixed line. But the problem is that there are also cells with normal data that will be different for each row. When I copy the formulas for an entire row, it will also copy the data from the cells without formulas. Therefore, I think it is best to use an identifier. I will put an "new row" in Column A of the source row. Do you think it is possible like this? Thanks, " wrote: Tom, Is the source row the last row of data in the sheet? Will it always be the last row? It is possible if that is the case. Alternatively, you could put it above the header row, into Row 1 and hide the row. Or is there some unique identifier? Are the formulas the same in every row, because it could be copied from anywhere if that is the case. The following assumes the formula rows is always the last row. Steven Sub InsRw() Dim Rng as Range Dim Sht as Worksheet Dim SrcRow as Range Set Sht = ActiveSheet Set Rng = ActiveCell.EntireRow Set SrcRow = Range("A" & Sht.Cells(Rows.Count, "A").End(xlUp).Row).EntireRow Sht.Unprotect Rng.Insert Shift:=xlDown SrcRow.Copy Rng.Offset(-1, 0) Set Rng = Rng.Offset(1, 0) Rng.Select Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub On Sep 25, 11:55 am, Tom82 wrote: Steven, Thanks for your tip. The fact that it inserts a row above the selected cell is very usefull, but there is still something else I need. I want to insert not just a white row, but a row with specific formulas that I already created at the bottom of the sheet (e.g. on row 100). So it has to insert row 100 above the selected cell. If the macro is executed once, this means that the next time it should insert row 101 as it shifted one row down due the insertion. Is this possible? Thanks " wrote: On Sep 24, 10:29 pm, Tom82 wrote: Hi, I created easy macro below to enter a predesigned (formats and formulas) row in a protected sheet and then protect it again. Now I would like to set the macro that it will insert the line above the cell in which I'm standing at the time that I activate the Macro. Furthermore, I would like to fix the row that I enter.... in this case it is row 65..but when I execute the Macro ones, it will become row 66... Can somebody tell me which changes I have to make in the codes?Thanks ActiveSheet.Unprotect ActiveWindow.SmallScroll Down:=21 Rows("65:65").Select Selection.Copy ActiveWindow.SmallScroll Down:=-21 Rows("20:20").Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hello Tom, I assume what you want is to keep the row you have selected as the 'active' row, to return to after you have inserted a row in the range above. You use Cut/Copy - do you want the selected row cut and inserted somewhere else, or were you just using that as a method to insert a row? The following code is untested (can't run it on my Mac at home) but should insert a row before the currently selected cell, then select the row again. Steven Sub InsRw() Dim Rng as Range Dim Sht as Worksheet Set Sht = ActiveSheet Set Rng = ActiveCell.EntireRow Sht.Unprotect Rng.Insert Shift:=xlDown Set Rng = Rng.Offset(1,0) Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub |
Macro to insert row in protected sheet
Steven,
The formula that you proposed works if I put an "x" as identifier in the Column A of the source row. It works perfectly, thanks for that. One additional question. I made a toolbar button for this macro for easy use... But I also like to have an undo button for when the row is inserted in the wrong place. Because if this is the case, the user will have to unprotect and then delete the row and protect again. I like to keep it as simple as possible (a button) for the user. Can you therefore tell me how to undo this Macro when necessary? Thanks " wrote: Tom, Is the source row the last row of data in the sheet? Will it always be the last row? It is possible if that is the case. Alternatively, you could put it above the header row, into Row 1 and hide the row. Or is there some unique identifier? Are the formulas the same in every row, because it could be copied from anywhere if that is the case. The following assumes the formula rows is always the last row. Steven Sub InsRw() Dim Rng as Range Dim Sht as Worksheet Dim SrcRow as Range Set Sht = ActiveSheet Set Rng = ActiveCell.EntireRow Set SrcRow = Range("A" & Sht.Cells(Rows.Count, "A").End(xlUp).Row).EntireRow Sht.Unprotect Rng.Insert Shift:=xlDown SrcRow.Copy Rng.Offset(-1, 0) Set Rng = Rng.Offset(1, 0) Rng.Select Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub On Sep 25, 11:55 am, Tom82 wrote: Steven, Thanks for your tip. The fact that it inserts a row above the selected cell is very usefull, but there is still something else I need. I want to insert not just a white row, but a row with specific formulas that I already created at the bottom of the sheet (e.g. on row 100). So it has to insert row 100 above the selected cell. If the macro is executed once, this means that the next time it should insert row 101 as it shifted one row down due the insertion. Is this possible? Thanks " wrote: On Sep 24, 10:29 pm, Tom82 wrote: Hi, I created easy macro below to enter a predesigned (formats and formulas) row in a protected sheet and then protect it again. Now I would like to set the macro that it will insert the line above the cell in which I'm standing at the time that I activate the Macro. Furthermore, I would like to fix the row that I enter.... in this case it is row 65..but when I execute the Macro ones, it will become row 66... Can somebody tell me which changes I have to make in the codes?Thanks ActiveSheet.Unprotect ActiveWindow.SmallScroll Down:=21 Rows("65:65").Select Selection.Copy ActiveWindow.SmallScroll Down:=-21 Rows("20:20").Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hello Tom, I assume what you want is to keep the row you have selected as the 'active' row, to return to after you have inserted a row in the range above. You use Cut/Copy - do you want the selected row cut and inserted somewhere else, or were you just using that as a method to insert a row? The following code is untested (can't run it on my Mac at home) but should insert a row before the currently selected cell, then select the row again. Steven Sub InsRw() Dim Rng as Range Dim Sht as Worksheet Set Sht = ActiveSheet Set Rng = ActiveCell.EntireRow Sht.Unprotect Rng.Insert Shift:=xlDown Set Rng = Rng.Offset(1,0) Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub |
Macro to insert row in protected sheet
On Sep 25, 4:23*pm, Tom82 wrote:
Steven, The formula that you proposed works if I put an "x" as identifier in the Column A of the source row. It works perfectly, thanks for that. One additional question. I made a toolbar button for this macro for easy use... But I also like to have an undo button for when the row is inserted in the wrong place. Because if this is the case, the user will have to unprotect and then delete the row and protect again. I like to keep it as simple as possible (a button) for the user. Can you therefore tell me how to undo this Macro when necessary? Thanks " wrote: Tom, Is the source row the last row of data in the sheet? Will it always be the last row? It is possible if that is the case. Alternatively, you could put it above the header row, into Row 1 and hide the row. Or is there some unique identifier? Are the formulas the same in every row, because it could be copied from anywhere if that is the case. The following assumes the formula rows is always the last row. Steven Sub InsRw() Dim Rng as Range Dim Sht as Worksheet Dim SrcRow as Range Set Sht = ActiveSheet Set Rng = ActiveCell.EntireRow Set SrcRow = Range("A" & Sht.Cells(Rows.Count, "A").End(xlUp).Row).EntireRow Sht.Unprotect Rng.Insert Shift:=xlDown SrcRow.Copy Rng.Offset(-1, 0) Set Rng = Rng.Offset(1, 0) Rng.Select Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub On Sep 25, 11:55 am, Tom82 wrote: Steven, Thanks for your tip. The fact that it inserts a row above the selected cell is very usefull, but there is still something else I need. I want to insert not just a white row, but a row with specific formulas that I already created at the bottom of the sheet (e.g. on row 100). So it has to insert row 100 above the selected cell. If the macro is executed once, this means that the next time it should insert row 101 as it shifted one row down due the insertion. Is this possible? Thanks " wrote: On Sep 24, 10:29 pm, Tom82 wrote: Hi, I created easy macro below to enter a predesigned (formats and formulas) row in a protected sheet and then protect it again. Now I would like to set the macro that it will insert the line above the cell in which I'm standing at the time that I activate the Macro. Furthermore, I would like to fix the row that I enter.... in this case it is row 65..but when I execute the Macro ones, it will become row 66... Can somebody tell me which changes I have to make in the codes?Thanks ActiveSheet.Unprotect * * ActiveWindow.SmallScroll Down:=21 * * Rows("65:65").Select * * Selection.Copy * * ActiveWindow.SmallScroll Down:=-21 * * Rows("20:20").Select * * Selection.Insert Shift:=xlDown * * Application.CutCopyMode = False * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hello Tom, I assume what you want is to keep the row you have selected as the 'active' row, to return to after you have inserted a row in the range above. You use Cut/Copy - do you want the selected row cut and inserted somewhere else, or were you just using that as a method to insert a row? The following code is untested (can't run it on my Mac at home) but should insert a row before the currently selected cell, then select the row again. Steven Sub InsRw() Dim Rng as Range Dim Sht as Worksheet Set Sht = ActiveSheet Set Rng = ActiveCell.EntireRow Sht.Unprotect Rng.Insert Shift:=xlDown Set Rng = Rng.Offset(1,0) Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hi Tom, Sorry for the delays, I've a large database project this week that has come to a head in the configuration stage. As for your question - There is no undo option for macros, which makes it problematic. We could have another flag in the A column - 'z' for example. When the macro runs it clears all 'z' values but puts it into the new row. If it is in error, when you run the "undo" macro, it unprotects the sheet, finds the row 'z' and deletes it. That can be done with a slight modification of the code you already have. Let me know if that works for you. Steven |
All times are GMT +1. The time now is 01:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com