Filling down problem in VB

[kate]

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

 

[Tom Ogilvy]

This would be one way:

Private Sub CommandButton1_Click()




Dim wks As Worksheet
Dim cell as Range
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 _
Or wks.Name = "Sheet1" Then

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

rng.Insert

For Each cell In rng.Offset(-1, 0) _
.Range("A1,B1,D1,G1:H1")
cell.FillDown
Next
End If
Next


End Sub

 

[ross]

hi,

Not 100%, but i dont think so. You could just delete the cell in the copied range?

good Luck
Ross