K
Kevin McM
I have workbook that I am trying to use with a macro form another post on
this form. It inserts a new row with the formulas and contents of the
selected row. The problem is that it copies the row below the one selected,
and because of this, I can not make a copy of row 2 below the column headers.
Any ideas? Thanks. Here is the code:
'/=======Start of Code==========================/
Sub InsertRowsAndFillFormulas()
'adds desired # of lines below the current line and
' copies the formulas to that/those lines
'added selection of more than one worksheet
' - Gary L. Brown
' - Kinneson Corp. 01/17/2001
' - modification from thread discussion in
' Microsoft.Public.Excel.Programming newsgroup
' on 01/17/2001
' Re: Insert Rows -- 1997/09/24 Mark Hill
' The original macro is described in
' http://www.geocities.com/davemcritchie/excel/insrtrow.htm
Dim vRows As Long
Dim sht As Worksheet, shts() As String, i As Long
' row selection based on active cell --
' rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select
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
'if you just want to add cells and not entire rows
' then delete ".EntireRow" in the following line
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
'insert rows on grouped worksheets
' rev. 2001-01-17 Gary Brown
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
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 remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
'reselect original group - Dave McRitchie 01/17/2001
Worksheets(shts).Select
End Sub
'/=======End of Code==========================/
this form. It inserts a new row with the formulas and contents of the
selected row. The problem is that it copies the row below the one selected,
and because of this, I can not make a copy of row 2 below the column headers.
Any ideas? Thanks. Here is the code:
'/=======Start of Code==========================/
Sub InsertRowsAndFillFormulas()
'adds desired # of lines below the current line and
' copies the formulas to that/those lines
'added selection of more than one worksheet
' - Gary L. Brown
' - Kinneson Corp. 01/17/2001
' - modification from thread discussion in
' Microsoft.Public.Excel.Programming newsgroup
' on 01/17/2001
' Re: Insert Rows -- 1997/09/24 Mark Hill
' The original macro is described in
' http://www.geocities.com/davemcritchie/excel/insrtrow.htm
Dim vRows As Long
Dim sht As Worksheet, shts() As String, i As Long
' row selection based on active cell --
' rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select
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
'if you just want to add cells and not entire rows
' then delete ".EntireRow" in the following line
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
'insert rows on grouped worksheets
' rev. 2001-01-17 Gary Brown
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
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 remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
'reselect original group - Dave McRitchie 01/17/2001
Worksheets(shts).Select
End Sub
'/=======End of Code==========================/