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

strangebiscuit

New Member
Joined
Nov 25, 2013
Messages
35
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:

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

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
hi, something like below? regards
Code:
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

New Member
Joined
Nov 25, 2013
Messages
35
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.

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

Watch MrExcel Video

Forum statistics

Threads
1,122,538
Messages
5,596,750
Members
414,097
Latest member
FaeFen

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