Macro for Inserting Rows

[Guest]

Hi, I have never posted before but hope that someone can help me with a
problem now that I have.

I am developing a spreadsheet in which I want to give users the power to
insert additional rows, which sounds easy enough. However what I want is to
do is have a macro they can use to do this, as for every row that is inserted
I need the formulas present in the existing rows to be copied into newly
inserted row. I don't think the users will have the awareness to copy the
formulas into the new rows themselves, hence I want to make it happen
automatically.

I found some VB script in another posting that has almost solved this for me
(below), but the problem is that running this inserts a row in the same row
position each time (row 18). What I want is for the user to either highlight
the row of their choosing or click into a cell in that row and for the macro
to insert a row in the position they have selected.

Can anyone suggest what I am doing wrong, please? I am a novice with VB
script so don't know where to start.

Rows("18:18").Select
Selection.Insert Shift:=xlDown
Range("H15").Select
INSERTPT = ActiveCell.Value
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("H18").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[Rob Edwards]

Hi,
The following will insert a row from the position they have
selected...

ActiveCell.EntireRow.Select
'Rows("18:18").Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(0, 7).Select
'Range("H15").Select
INSERTPT = ActiveCell.Value
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(3, 0).Select
'Range("H18").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

I have replaced the original code with mine by making them comments
(i.e. ').

Rob Edwards

Always look on the bright side of life!

 

[Bob Phillips]

ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Don Guillett]

Here is one I use to copy the row above to a new row at the bottom

Sub newline()
With Cells([ChecksA].Rows.count + 7, 1)
.Offset(0) = .Offset(-1)'copies above date
End With
End Sub

Can be modified to insert a row.

 

[Guest]

Thanks Bob,

Your script does almost what I want, and it's my own fault for not being
specific that it isn't quite right.

Each row in the spreadsheet has numeric and text values in the first few
columns, which I don't want to copy into the inserted row. However, there are
formula in subsequent columns of the row that I do wish to copy into the
inserted row, along with some conditional formatting.

I suspect what I might need is a combination of the script from Rob and that
from your own posting, although it could be that what I am after is something
that simply can't be done.

As before, any suggestions you have would be gratefully received.

 

[Guest]

ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).EntireRow.Copy
Cells(ActiveCell.Row, 1).PasteSpecial xlFormulas

 

[Guest]

Thanks to all, with your help I now have this working exactly as I want it.

Dickie

Here is one I use to copy the row above to a new row at the bottom

Sub newline()
With Cells([ChecksA].Rows.count + 7, 1)
.Offset(0) = .Offset(-1)'copies above date
End With
End Sub

Can be modified to insert a row.
--
Don Guillett
SalesAid Software
(e-mail address removed)

Hi, I have never posted before but hope that someone can help me with a
problem now that I have.

I am developing a spreadsheet in which I want to give users the power to
insert additional rows, which sounds easy enough. However what I want is
to
do is have a macro they can use to do this, as for every row that is
inserted
I need the formulas present in the existing rows to be copied into newly
inserted row. I don't think the users will have the awareness to copy the
formulas into the new rows themselves, hence I want to make it happen
automatically.

I found some VB script in another posting that has almost solved this for
me
(below), but the problem is that running this inserts a row in the same
row
position each time (row 18). What I want is for the user to either
highlight
the row of their choosing or click into a cell in that row and for the
macro
to insert a row in the position they have selected.

Can anyone suggest what I am doing wrong, please? I am a novice with VB
script so don't know where to start.

Rows("18:18").Select
Selection.Insert Shift:=xlDown
Range("H15").Select
INSERTPT = ActiveCell.Value
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("H18").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False