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

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
Hi all,

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

VBA Code:
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
391
Office Version
  1. 2016
Platform
  1. Windows
Hello Kayslover,
tell me what you think about...

VBA Code:
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
 
Last edited:
Solution

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
Excel Max,

Will try it out tomorrow and report back.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
Excel Max,

Worked a treat, I appreciate your kind assistance.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,670
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
   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

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Fluff,

Much appreciated as expected worked a charm.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,670
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
391
Office Version
  1. 2016
Platform
  1. Windows
I'm glad that you are satisfied with solutions.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top