Copy/Insert rows with formulas

[GregR]

I have a macro that copies the last entered row and inserts rows based
on a number from an input box. It works great except two of the rows I
want the copied formulas to increment to the next value and two of the
rows not to increment. Right now all of the rows increment. The
inserted rows start at row 8 and are incremented by the number in the
input box. Also, if I wanted the input box to default to the value in
"C2", how would I do it? TIA

Greg

 

[Don Guillett]

As ALWAYS, pls post your macro for comments

 

[GregR]

Don, here it is. TIA

Sub Insert_SplitRows()

' InsertRowsAboveTotals()
' InsertRowsAndFillFormulas(Optional vRows As Long)
Dim vRows As Integer
Dim Mycount As Integer
Dim irow As Long, i As Long
'Mycount = Range("C2").Value

' row selection based on "Totals" cell
Columns("C:C").Find(what:="Totals", after:=Range("C4"),
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Offset(-2, 0).Activate
ActiveCell.EntireRow.Select
If vRows <> 1 Then
vRows = Application.InputBox(prompt:= _
"Enter Number Of Rows To Insert." & vbNewLine & _
"Or 'OK' For Default Value." & vbNewLine & _
"Or 'Cancel'.", _
Title:="Add Rows", Default:="")


If vRows = False Then Exit Sub

End If

'Set Mycount = ActiveSheet.Range("C2").Value
' If vRows = "" Then
' vRows = Mycount
'End If


ActiveSheet.Select
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 to remove the non-formulas
'Selection.Offset(1).Resize(vRows).EntireRow. _
'SpecialCells(xlConstants).ClearContents
irow = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate
End Sub

Greg

 

[GregR]

In my intial post I said rows I meant columns. Sorry for the confusion.

Greg

 

[GregR]

That is I want two of the columns to copy as a constant and two of the
columns to increment the value from the previous row. I hope all this
makes sense.

Greg