Need to calculate sum & division till end of data in sheet1

[aleanboy]

Hi,

In the attached file I have code so when I click on submit it will insert rows & also calculate subtotal & grand total.
But I need few more calculations as shown in Sheet “Report”.
Note: Highlighted in yellow rows are the ones that I am looking for.
And note: Information displayed in Sheet “Report” is final & which I will be presenting to my managers.
So instead of inserting as “Subtotal & Grand total” , could you please help me to update the columns “Area Director, Region & Leadership names as shown in report sheet”. There is no logic behind those name. so based on sequence number those column “A”, “B” & “C” need to be filled.

Note: Similar to the calculations of subtotal & grand total that is been made under columns “E”, “F” & G”. It is accurate, how can I have formulas for remaining columns (I have specified the formulas in Sheet “Report”

Please advise.

[SamT]

Not enough information.

I am guessing that you are starting with a single sheet ("Raw") and inserting 6 rows and creating a command button, then renaming that sheet "Sheet1". But you might e making a copy of Raw and naiming the copy Sheet1, then inserting the rows and command button.

Then you click the button and run the two subs which format the sheet and adds some rows and formulas. When all that and what you need help with, the sheet is named "Report."

Are you starting with a workbook with a sheet named "Raw" in it?

Do you want to end up with a workbook with a sheet named Report in it that is formatted like the sheet "Report" in your upload?

Assuming those are both true, what I would do is in a new Master Workbook, put all the code to do the task. All that code would work on the original workbook with the sheet "Raw" in it.

That code would have several arrays:

GrandTotalInsertRows = Array(65, 53, 30, 11)
GrandTotalFormulaRows = Array (68, 56, 32, 12) 'Verify row numbers after insert rows at GrandTotalRows)
SubTotalInsertRows = Array( Verify Row numbers to insert after after insert rows at GrandTotalRows
SubTotalFormulaRows = Array(Verify as above)
SumFormulaColumns = Array("E", "F", "G", "H", etc)
Directors = Array( according to GrandTotalFormulaRows, "WEVO TOTAL", "DAVID TURIS", Etc)
Leadership = Array(Acording to SubTotalFormula Rows, "", "Asif Salam", "Tae Kang", etc)
SubRegion = Array(According to Leadership, etc)
Region = Array(According to Directors)

Steps:

1. Insert the 5 rows at the top.
2. Insert the GrandTotalInsertRows rows, Format each row as you go
3. Add the Directors and Regions in GrandTotalFormulaRows(r)
4. In Cell(GrandTotalFormulaRows(r), SumFormulaColumns(c)) use = Sum(Range(Cell.End(xlUp), Cell.Offset(-1,))
5. Insert SubTotalInsertRows rows, Format each Row as you go
6. Add the LeaderShip and Subregions in SubTotalFormulaRow(r)
7. In Cell(SubTotalFormulaRow(r), SumFormulaColumns) use = Sum(Range((Cell.End(xlUp), Cell.Offset(-1,))
8. Rename Sheet ("RAW")
9. Save Raw book as new name

Insert Rows Example

For r = Lbound(GrandTotalInsertRows) to Ubound(GrandTotalInsertRows)
Rows(GrandTotalInsertRows(r)).Insert
Next r

Formula example

Dim r as Long
Dim c As long
Dim WF As Object
Set WF = Application.WorksheetFunction

Application.ScreenUpdating = False
For r = LBound(GrandTotalFormulaRows) to UBound(GrandTotalFormulaRows)
For c = Lbound(SumFormulaColumns) to UBound(SumFormulaColumns)
Cell(GrandTotalFormulaRows(r), SumFormulaColumns(c))  = WF.Sum(Range((Cell.End(xlUp), Cell.Offset(-1,))
Next c
Next r
Application.ScreenUpdating = True