Bob, I have found the macro I had lost but, having pasted it into the
PERSONAL.XLS module it will not run - have I missed something when I copied
it into the Word doc where I saved it?
I do hope you can help - it is imortant for me to get it going soon and it
is way beyond me - as you can see it is the work of others.
€˜Insert row reproducing formulae from row below
Sub InsertRowsAndFillFormulas(Optional vRows As Long)
Application.ScreenUpdating = False
' Documented:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Insert Rows -- 1997/09/24 Mark Hill
'Dim vRows As Integer (This put at the beginning of this Module
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows < 1 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
If vRows = False Then Exit Sub
End If
'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line
'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Integer
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedShee ts
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name
Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown
Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault
On Error Resume Next 'to handle no constants in range -- John McKee
2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
Application.Goto reference:="R[1]C1"
End Sub
Thanks
camlad
"Bob Phillips" wrote:
Sub InsertNewRow()
ActiveCell.EntireRow.Insert
Rows(ActiveCell.Row + 1).Copy
Cells(ActiveCell.Row, 1).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub
--
__________________________________
HTH
Bob
"camlad" wrote in message
...
Catastrophe! I did not keep a copy of a macro which inserted a row above
and
copied everything from the row below into the new row except any contents
and
fill color. The formula in some of the cells was copied but none of the
contents so the new row was ready for entries
One of you helped with this several years ago and it has been very
useful -
please help again.