Option Explicit
Sub NewRow()

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

For wCtr = 6 To Worksheets.Count
Set wks = Worksheets(wCtr)
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 wCtr

End Sub


aussiegirlone wrote:

Can you make it skip the first 5 sheets and start on the 6th

"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


--

Dave Peterson