Insert Formula in every sheet in a workbook and remove formatting.

[Kayslover]

Hi all,

I have got a macro that inserts a new row in each and every sheet within a workbook using the following:-

For Each ws In Worksheets
With ws
.Rows(2).Insert Shift:=xlDown
Rows("2:2").Select
End With
Next

Can someone help with inserting a formula in each and every sheet in H2 where the formula is =AVERAGE(H2:Last Row)

Where last row is the last row that has data. Please note some sheets may not have any data after row 3.

Additionally, having inserted row 2 in each sheet, I note that cells are formatted with the colour and fill colour from the previous row. Is there any way that the fill colour is set to “No Fill” and the colour is set to black?

Can what I am asking for be coded with the loop I have?

Thanks in advance for any assistance.

 

[EXCEL MAX]

Hello Kayslover,
tell me what you think about...

Sub InsertFormulaH2()

    Dim ws As Worksheet
    Dim varNRows As Long
  
    For Each ws In Worksheets
        varNRows = ws.Cells(Rows.Count, "H").End(xlUp).Row
        With ws
            .Rows(2).Insert Shift:=xlDown
            .Range("H2").Formula = "=AVERAGE(H3:H" & varNRows + 1 & ")"
            .Rows("2:2").Interior.Color = xlNone
            .Range("H2").Font.Color = RGB(0, 0, 0)
        End With
    Next

End Sub

 

[Kayslover]

Excel Max,

Will try it out tomorrow and report back.

 

[Kayslover]

Excel Max,

Worked a treat, I appreciate your kind assistance.

 

[Kayslover]

Excel Max,

One more question comes to mind. How do I exclude sheets called Buylist and Lookup from being worked on in the loop?

 

[Fluff]

How about

   For Each ws In Worksheets
      If ws.Name <> "Buylist" And ws.Name <> "Lookup" Then
         varNRows = ws.Cells(Rows.count, "H").End(xlUp).Row
         With ws
            .Rows(2).Insert Shift:=xlDown
            .Range("H2").Formula = "=AVERAGE(H3:H" & varNRows + 1 & ")"
            .Rows("2:2").Interior.Color = xlNone
            .Range("H2").Font.Color = RGB(0, 0, 0)
         End With
      End If
   Next ws

 

[Kayslover]

Fluff,

Much appreciated as expected worked a charm.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[EXCEL MAX]

I'm glad that you are satisfied with solutions.