Insert row on multiple sheets

[aussiegirlone]

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]

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]

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

 

[aussiegirlone]

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]

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]

Good Morning Dave
I see this morning in my email you replied again to my need for help on this
matter. I thought you were not going to respond anymore and therefore, I had
re entered my question for help yesterday. Responding to your email this
morning and Testing the second code you gave works very well. However, My
question has since been resolved and I Thankyou very much and those that
helped me.
PS: I hope you have a good day
Sincerely
Aussiegirlone

 

[Dave Peterson]

If you're going to start a new thread that continues an existing discussion,
then it would have been nice for you to post a followup to the existing thread
with a note saying that.

Then I wouldn't have wasted my time with my response.

Good Morning Dave
I see this morning in my email you replied again to my need for help on this
matter. I thought you were not going to respond anymore and therefore, I had
re entered my question for help yesterday. Responding to your email this
morning and Testing the second code you gave works very well. However, My
question has since been resolved and I Thankyou very much and those that
helped me.
PS: I hope you have a good day
Sincerely
Aussiegirlone