Filling down

[katie]

The following piece of code allows me to enter a new row
in exactly the same position on multiple sheets, it then
copies down the data from the above row - therefore
maintaing all formulae.
However, is it possible to specify which cells from above
eg columns A, B, and D need to be filled down, but not C
and E?



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 = 6 Then
MsgBox "Can't fill down from above row 6."
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
..

 

[Bernie Deitrick]

Katie,

At the end of your code, you will need to clear the cells in columns C and E
of the new row.

Change

rng.Offset(-1, 0).FillDown

to:

With rng.Offset(-1, 0)
.FillDown
.Range("C1").ClearContents
.Range("E1").ClearContents
End With


HTH,
Bernie
MS Excel MVP