loop through all worksheets and add sums/formulas at bottom of each

[strangebiscuit]

I'm working on a macro that does two things. First, it will split data into multiple sheets (with each sheet based on the value of a specific column). Second, it will loop through all the sheets and add certain sums and formulas to the bottom of each sheet.

I found a very helpful bit of code to split the sheets which I modified slightly and works fine. And I found another bit of code that will cycle through all the sheets in the document. I'm just not sure how to add the necessary sums/formula fields to the end of each sheet.

The code to loop through the sheets is this:

Sub WorksheetLoop2()


 ' Declare Current as a worksheet object variable.
 Dim Current As Worksheet


 ' Loop through all of the worksheets in the active workbook.
 For Each Current In Worksheets


    ' Insert your code here.
    ' This line displays the worksheet name in a message box.
    MsgBox Current.Name
 Next


End Sub

So in the "insert your code here" part I need code that:

1. Finds the last row of data on the sheet
2. Go down one or two rows from there
3. In column A of that row add a label that just says "Total Points"
4. In column B of that row make a cell that sums the total of all the values in column H (which are simple integers)
5. On the next row of column A add a label that says "Overrides"
6. On that same row in column B leave a blank space where user can put in their own number.
7. On the next row of column A add a label that says "Total"
8. On that same row in column B make the cell give the sum of the Total Points and Overrides cells that were just added directly above.

And I think I can figure out the rest if I get that far.

Any help or advice would be greatly appreciated. Thanks very much!

 

[Fazza]

hi, something like below? regards

With Current.Cells(Current.Cells.Find(What:="*", AFter:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2, 1)
  .Resize(3).Value = Application.Transpose(Array("Total Points", "Overrides", "Total"))
  .Offset(, 1).FormulaR1C1 = "=SUM(R1C8,R[-1]C8)"
  .Offset(2, 1).FormulaR1C1 = "=SUM(R[-2]C,R[-1]C)"
End With

 

[strangebiscuit]

Wow, that's just about perfect. I just changed the comma in the first R1C1 formula to a colon so it sums the whole column. It works great, thanks so much!

I've just got one more question...I added some extra formulas and stuff and I want to format all these formula fields (except Total Points) as currency. I just stuck in 5 Offset commands at the bottom simply to get it working. But is there a way to format these 5 cells in one line as a range?

Thanks again for the help you've given already. It really saved me and is very much appreciated.

Sub LoopAndInsert()


 ' Declare Current as a worksheet object variable.
 Dim Current As Worksheet


 ' Loop through all of the worksheets in the active workbook.
 For Each Current In Worksheets


    With Current.Cells(Current.Cells.Find(What:="*", AFter:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2, 1)
      .Resize(6).Value = Application.Transpose(Array("Total Points", "Base", "Commissions", "Bonus", "Overrides", "Total"))
      .Resize(6).Font.Bold = True
      ' Total Points Formula
      .Offset(, 1).FormulaR1C1 = "=SUM(R1C8:R[-1]C8)"
      ' Commissions Formula
      .Offset(2, 1).FormulaR1C1 = "=IF(R[-2]C < 10, 0, IF(R[-2]C < 15, R[-2]C*15, IF(R[-2]C < 25, R[-2]C*15, IF(R[-2]C < 30, R[-2]C*20, IF(R[-2]C < 35, R[-2]C*25, IF(R[-2]C < 40, R[-2]C*30, R[-2]C*35))))))"
      ' Bonus Formula
      .Offset(3, 1).FormulaR1C1 = "=IF(R[-3]C > 14, 225, 0)"
      ' Total
      .Offset(5, 1).FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
      ' Set number formats to currency
      .Offset(1, 1).NumberFormat = "$#,##0.00"
      .Offset(2, 1).NumberFormat = "$#,##0.00"
      .Offset(3, 1).NumberFormat = "$#,##0.00"
      .Offset(4, 1).NumberFormat = "$#,##0.00"
      .Offset(5, 1).NumberFormat = "$#,##0.00"
    End With
    
 Next


End Sub

 

[Fazza]

.offset(1,1).resize(5).numberformat = "...

 

[strangebiscuit]

Beautiful. I owe you one. Thanks so much!

 

[Fazza]

you're welcome