# 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

' 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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### 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
188
Replies
5
Views
101
Replies
3
Views
106
Replies
5
Views
123
Replies
11
Views
97

1,128,172
Messages
5,629,134
Members
416,365
Latest member
dof

### 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.

### Which adblocker are you using?

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

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