Dave the code works well , but is it possible to insert the row starting at
the 6th sheet instead of the 1st sheet? Please

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub NewRow()

Dim EndRowA As Long
Dim NextRowAF As Long
Dim wks As Worksheet
Dim iRow As Long

For Each wks In ActiveWorkbook.Worksheets
With wks
EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row
NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1
.Cells(NextRowAF, "AC").Value = "Total"
.Cells(NextRowAF, "AF").Formula _
= "=sum(AF5:AF" & NextRowAF - 1 & ")"
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC"))
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 32
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = 2
.Borders.Weight = xlThin
End With

For iRow = NextRowAF + 1 To 32
If Application.CountA(.Rows(iRow)) = 0 Then
.Rows(iRow).Interior.ColorIndex = 2
End If
Next iRow

.Rows("5:32").RowHeight = 12.75
End With
Next wks

End Sub

Those dots in front of the .cells(), .rows(), .Font, ... are very important.
They indicate that this property/method belongs to the object in the previous
With statement. (Either the looping worksheet or the union.)


aussiegirlone wrote:

If someone can help me please?
At the moment I am using 'Sheets("Mine").Activate' to insert a row on a
specified sheet, but now I would like to insert the row using the code below
on multiple sheets e.g (1st to last sheet) without having to name each sheet.
Can this be done?

Sub NewRow()
EndRow = Cells(Rows.Count, 1).End(xlUp).Row
n = Cells(Rows.Count, "AF").End(xlUp).Row + 1
Cells(n, "AC").Value = "Total"
Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")"
Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True
Union(Cells(n, "AF"), Cells(n, "AC")).Font.ColorIndex = 2
Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 32
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.ColorIndex = 2
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin

For i = n + 1 To 32
If Application.CountA(Rows(i)) = 0 Then
Rows(i).Interior.ColorIndex = 2
End If
Next i
Range("A5").Select
Rows("5:32").Select
Selection.RowHeight = 12.75
Range("A5").Select

End Sub

--

Dave Peterson