Inserting rows at variable points
 

The macro below allows the user to insert 8 rows on a diary card type form
when needed, using the last blank row on the form. The macro is intended to
be run over and over, however, unless I make the references variable, the
rows will be inserted in the middle of data entry. I don't know how to make
the last row and the inserted rows always occur at the end of the entries. I
can't count the rows to determine the end because I have other entries in
rows below, representing the next page of the form.

Any help is appreciated.

Sub Insertrows1()

ActiveSheet.Unprotect
Range("A23").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Rows("45:45").Select<-------------------- I want this to be one up from
end down, end down, which will be last row of the form.

Selection.Copy
Rows("46:54").Select<---------------------- I want this to be 8 rows
after the last row
Selection.Insert Shift:=xlDown
Range("A46").Select
Selection.End(xlDown).Select
Range("A54").Select<---------- I want to return the user to the last
row, before the insertion of new rows.
ActiveSheet.Protect
End Sub

Inserting rows at variable points
 

How about doing End(xlup) instead.

Range("A65536").End(xlup).Offset(-1,0).Select

Selection.Resize(8).EntireRow.Insert

Range("A65536").End(xlup).Select

--
Regards,
Tom Ogilvy


"Janet H" wrote in message
...
The macro below allows the user to insert 8 rows on a diary card type form
when needed, using the last blank row on the form. The macro is intended
to
be run over and over, however, unless I make the references variable, the
rows will be inserted in the middle of data entry. I don't know how to
make
the last row and the inserted rows always occur at the end of the entries.
I
can't count the rows to determine the end because I have other entries in
rows below, representing the next page of the form.

Any help is appreciated.

Sub Insertrows1()

ActiveSheet.Unprotect
Range("A23").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Rows("45:45").Select<-------------------- I want this to be one up
from
end down, end down, which will be last row of the form.

Selection.Copy
Rows("46:54").Select<---------------------- I want this to be 8 rows
after the last row
Selection.Insert Shift:=xlDown
Range("A46").Select
Selection.End(xlDown).Select
Range("A54").Select<---------- I want to return the user to the last
row, before the insertion of new rows.
ActiveSheet.Protect
End Sub

Inserting rows at variable points
 

That won't work because I have another form which represents the 2nd page of
the document.

End down, end down, 1 up will work because it will always put me at the last
row of form 1 (I think), even if they've previously inserted rows. My
instructions will tell them to to insert before they enter data on the last
row.

I need help with the "1 up"

Thanks.

"Tom Ogilvy" wrote:

How about doing End(xlup) instead.

Range("A65536").End(xlup).Offset(-1,0).Select

Selection.Resize(8).EntireRow.Insert

Range("A65536").End(xlup).Select

--
Regards,
Tom Ogilvy


"Janet H" wrote in message
...
The macro below allows the user to insert 8 rows on a diary card type form
when needed, using the last blank row on the form. The macro is intended
to
be run over and over, however, unless I make the references variable, the
rows will be inserted in the middle of data entry. I don't know how to
make
the last row and the inserted rows always occur at the end of the entries.
I
can't count the rows to determine the end because I have other entries in
rows below, representing the next page of the form.

Any help is appreciated.

Sub Insertrows1()

ActiveSheet.Unprotect
Range("A23").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Rows("45:45").Select<-------------------- I want this to be one up
from
end down, end down, which will be last row of the form.

Selection.Copy
Rows("46:54").Select<---------------------- I want this to be 8 rows
after the last row
Selection.Insert Shift:=xlDown
Range("A46").Select
Selection.End(xlDown).Select
Range("A54").Select<---------- I want to return the user to the last
row, before the insertion of new rows.
ActiveSheet.Protect
End Sub

Inserting rows at variable points
 

Range("A65536").End(xlup).Offset(-1,0).Select

the offset(-1,0).Select is the one up part.

--
Regards,
Tom Ogilvy

"Janet H" wrote in message
...
That won't work because I have another form which represents the 2nd page
of
the document.

End down, end down, 1 up will work because it will always put me at the
last
row of form 1 (I think), even if they've previously inserted rows. My
instructions will tell them to to insert before they enter data on the
last
row.

I need help with the "1 up"

Thanks.

"Tom Ogilvy" wrote:

How about doing End(xlup) instead.

Range("A65536").End(xlup).Offset(-1,0).Select

Selection.Resize(8).EntireRow.Insert

Range("A65536").End(xlup).Select

--
Regards,
Tom Ogilvy


"Janet H" wrote in message
...
The macro below allows the user to insert 8 rows on a diary card type
form
when needed, using the last blank row on the form. The macro is
intended
to
be run over and over, however, unless I make the references variable,
the
rows will be inserted in the middle of data entry. I don't know how to
make
the last row and the inserted rows always occur at the end of the
entries.
I
can't count the rows to determine the end because I have other entries
in
rows below, representing the next page of the form.

Any help is appreciated.

Sub Insertrows1()

ActiveSheet.Unprotect
Range("A23").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Rows("45:45").Select<-------------------- I want this to be one up
from
end down, end down, which will be last row of the form.

Selection.Copy
Rows("46:54").Select<---------------------- I want this to be 8
rows
after the last row
Selection.Insert Shift:=xlDown
Range("A46").Select
Selection.End(xlDown).Select
Range("A54").Select<---------- I want to return the user to the
last
row, before the insertion of new rows.
ActiveSheet.Protect
End Sub

Inserting rows at variable points
 

That wasn't it. It takes me to the wrong section of the spreadsheet.

This is what I ended up doing -

Range("A23").Select
Selection.End(xlDown).Select
Selection.End(xlDown).EntireRow.Offset(-1, 0).Select
Selection.Copy
Selection.Resize(8).EntireRow.Insert
Selection.End(xlUp).Select


"Tom Ogilvy" wrote:

Range("A65536").End(xlup).Offset(-1,0).Select

the offset(-1,0).Select is the one up part.

--
Regards,
Tom Ogilvy

"Janet H" wrote in message
...
That won't work because I have another form which represents the 2nd page
of
the document.

End down, end down, 1 up will work because it will always put me at the
last
row of form 1 (I think), even if they've previously inserted rows. My
instructions will tell them to to insert before they enter data on the
last
row.

I need help with the "1 up"

Thanks.

"Tom Ogilvy" wrote:

How about doing End(xlup) instead.

Range("A65536").End(xlup).Offset(-1,0).Select

Selection.Resize(8).EntireRow.Insert

Range("A65536").End(xlup).Select

--
Regards,
Tom Ogilvy


"Janet H" wrote in message
...
The macro below allows the user to insert 8 rows on a diary card type
form
when needed, using the last blank row on the form. The macro is
intended
to
be run over and over, however, unless I make the references variable,
the
rows will be inserted in the middle of data entry. I don't know how to
make
the last row and the inserted rows always occur at the end of the
entries.
I
can't count the rows to determine the end because I have other entries
in
rows below, representing the next page of the form.

Any help is appreciated.

Sub Insertrows1()

ActiveSheet.Unprotect
Range("A23").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Rows("45:45").Select<-------------------- I want this to be one up
from
end down, end down, which will be last row of the form.

Selection.Copy
Rows("46:54").Select<---------------------- I want this to be 8
rows
after the last row
Selection.Insert Shift:=xlDown
Range("A46").Select
Selection.End(xlDown).Select
Range("A54").Select<---------- I want to return the user to the
last
row, before the insertion of new rows.
ActiveSheet.Protect
End Sub