code for new rows and filling down

[Lucy]

I have the following piece of code that inserts a new row
in a given position (the active cell row in current
worksheet) on each of the 8 listed worksheets below.
When the new row is inserted, the code then 'pulls down'
formulae from the above row - this is where the problem
lies, i only want certain cells to copy down from above,
eg where there is a formulae that needs to be copied down,
i do not want manual entry cells to copy actual data down
from above.
How can i specify which specific cells to copy down?

OR

Is there a method by which i can fill down - as the coding
presently does, and then clear the contents of each cell -
but leave the formulae present?

Many Thanks
Lucy



Private Sub CommandButton1_Click()




Dim wks As Worksheet

Dim l_Row As Long
Dim rng As Range

ActiveCell.Select
l_Row = ActiveCell.Row


If l_Row = ActiveSheet.Rows.Count Then
MsgBox "Can't add any more rows!"
Exit Sub
ElseIf l_Row = 1 Then
MsgBox "Can't fill down from above row 1."
Exit Sub
End If



For Each wks In ThisWorkbook.Worksheets

If wks.Name = "Year Summary 02-03" _
Or wks.Name = "Year Summary 03-04" _
Or wks.Name = "Budgeted Hours" _
Or wks.Name = "Associates Hours (actuals)" _
Or wks.Name = "Directors Hours (actuals)" _
Or wks.Name = "Invoices (actuals)" _
Or wks.Name = "Project Costs" _
Or wks.Name = ActiveSheet.Name Then

Set rng = wks.Cells(l_Row, 1).EntireRow

rng.Insert

rng.Offset(-1, 0).FillDown

End If
Next


End Sub

 

[JE McGimpsey]

Take a look at David McRitchie's InsrtRow():


http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[BrianB]

I suggest you add code something like this which checks if a cel
contains a formula and clears it if not.


Code
-------------------
Sub test()
Dim f As String
For Each c In ActiveSheet.Rows(ActiveCell.Row).Cells
f = c.Formula
If InStr(1, f, "=", 1) = 0 Then
c.ClearContents
End If
Next
End Sub

 

[Dave Peterson]

Every once in awhile, I use a cell formatted as text that starts with an equal
sign (or just '====== (leading apostrophe)).

Instead of checking the first character, you could just check if really had a
formula:

Option Explicit
Sub test2()

Dim f As String
Dim c As Range

For Each c In ActiveCell.EntireRow.Cells
If c.HasFormula Then
c.ClearContents
End If
Next

End Sub

Another way is to look for all the formulas all at once. And just clear them en
masse.

Sub test3()

Dim f As String
Dim c As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = ActiveCell.EntireRow.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
'no formulas
Else
myRng.ClearContents
End If

End Sub