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

#### strangebiscuit

##### New Member
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:

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Fazza

##### MrExcel MVP
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
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``````

#### Fazza

##### MrExcel MVP
.offset(1,1).resize(5).numberformat = "... #### strangebiscuit

##### New Member
Beautiful. I owe you one. Thanks so much!

you're welcome

Replies
2
Views
179
Replies
5
Views
94
Replies
3
Views
101
Replies
5
Views
109
Replies
11
Views
93

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

### Share this page ### 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