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

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

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

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

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

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

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