copying down

[katie]

The following code allows me to select a row on sheet 1,
and then run the code and cause a new row to be created in
the identical place on the following 8 worksheets.




Private Sub CommandButton1_Click()

ActiveCell.Resize(1, 1).EntireRow.Insert

Worksheets("Year Summary 02-03").Rows
(ActiveCell.Row).EntireRow.Insert

Worksheets("Year Summary 03-04").Rows
(ActiveCell.Row).EntireRow.Insert

Worksheets("Budgeted Hours").Rows
(ActiveCell.Row).EntireRow.Insert

Worksheets("Associates Hours (actuals)").Rows
(ActiveCell.Row).EntireRow.Insert

Worksheets("Directors Hours (actuals)").Rows
(ActiveCell.Row).EntireRow.Insert

Worksheets("Invoices (actuals)").Rows
(ActiveCell.Row).EntireRow.Insert

Worksheets("Project Costs").Rows
(ActiveCell.Row).EntireRow.Insert

End Sub



Any ideas how this can be extended to make the new
rows 'copy down' the formulae from the row above where
they are created.

Thanks.

 

[Hank Scorpio]

The following code allows me to select a row on sheet 1,
and then run the code and cause a new row to be created in
the identical place on the following 8 worksheets.

[Snip code for insertion of a row on 8 specified sheets]

Any ideas how this can be extended to make the new
rows 'copy down' the formulae from the row above where
they are created.

Something like this is one way:

Private Sub CommandButton1_Click()

Dim wks As Worksheet

Dim l_Row As Long
Dim rng As Range

'Make sure that a cell has the focus.
ActiveCell.Select

'Get the row number of the active cell.
l_Row = ActiveCell.Row

'Can't do this if you're at the bottom of the sheet.
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

'Loop through all of the worksheets.
For Each wks In ThisWorkbook.Worksheets

'Dump the following IF condition and the End If
'if you really just want this done on
'ALL sheets in the book. At this point I'm assuming
'that you only want it done
'on specific sheets.

'Also I don't like the idea of using worksheet
'names; someone may change them. Consider using
'CodeNames instead. (They're the sheet names you
'can see in brackets when you have the Properties
'window open in the VB Editor. Look in on-line help
'for information about using CodeName properties.)

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 the rng variable to the row that
'you're on.
Set rng = wks.Cells(l_Row, 1).EntireRow
'Insert the new row.
rng.Insert

'Fill down from the row above, which we
'identify through the Offset property.
rng.Offset(-1, 0).FillDown

End If
Next

End Sub