Insert formulas in the all rows of a column depending on another column that has data.

[Kayslover]

I have a macro that I got off the Internet that creates a sheet called “Summary-Sheet”.

The sheet is created by going through the workbook and for each Sheet, it inserts the sheet name in Sheet “Summary-Sheet” along with a couple pieces of data in Columns B and C.

Summary-Sheet has data in columns A, B and C and Row 1 contains headings.

In column E, I would like to have the formula =D2*E2. This formula would have to go down as far as the last row that has data in Column A.

I would like to have a formula in the last row +1 in Column E that would sum all the rows.

Appreciate any help offered.

 

[Fluff]

Ok, try

Sub Kayslover()
   With Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=rc[-2]*rc[-1]"
      With .Offset(.Rows.Count).Resize(1)
         .FormulaR1C1 = "=sum(r2c:r[-1]c)"
         .Offset(, -4).Value = "TOTAL"
         With .Offset(, -4).Resize(, 5)
            .Font.Color = vbRed
            .Font.Bold = True
         End With
      End With
   End With
End Sub

 

[Kayslover]

Fluff,

Worked like a charm, many thanks for your time and effort, really apprecaited.

 

[Fluff]

You're welcome & thanks for the feedback.