Sum different sized (employee) teams
 

Hi,

I have some VB code (an answer from another question) written by vezerid
which I found very useful and have used in my sheet.

On the extra row that is being created - is it possible to sum the cells
above (of
certain columns) please?
I've used the VB below to put an extra row in after every change in team
name within my organisation (column A). Some teams have 10 people in, while
others have 100+ so putting this blank row automatically is very useful to
separate.
I then go through the sheet and manually sum the columns required (T, U and
BK)
for each team. I've tried to amend the code myself but I can't seem
to get it to work properly, as some of the 'sums' are for the 10 rows above
and then some are for 100.

Is it possible to amend the code to include the sum for these 3 colums
please?

Thanks in advance,

AW


"vezerid" wrote:

Sub InsertRows()

StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
i = i + 1
End If
i = i + 1
Wend
End Sub

Sum different sized (employee) teams
 

This will put sums on all but the last set of data. Sorry its not a complete
solution.

Sub InsertRows()

StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
x = StartRow
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")"
Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")"
Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")"
i = i + 1
x = i
End If
i = i + 1
Wend
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ArcticWolf" wrote:

Hi,

I have some VB code (an answer from another question) written by vezerid
which I found very useful and have used in my sheet.

On the extra row that is being created - is it possible to sum the cells
above (of
certain columns) please?
I've used the VB below to put an extra row in after every change in team
name within my organisation (column A). Some teams have 10 people in, while
others have 100+ so putting this blank row automatically is very useful to
separate.
I then go through the sheet and manually sum the columns required (T, U and
BK)
for each team. I've tried to amend the code myself but I can't seem
to get it to work properly, as some of the 'sums' are for the 10 rows above
and then some are for 100.

Is it possible to amend the code to include the sum for these 3 colums
please?

Thanks in advance,

AW


"vezerid" wrote:

Sub InsertRows()

StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
i = i + 1
End If
i = i + 1
Wend
End Sub

Sum different sized (employee) teams
 

Complete solution. Sorry I missed this earlier.

Sub InsertRows()

StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
x = StartRow
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")"
Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")"
Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")"
i = i + 1

x = i
End If
i = i + 1
Wend

Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")"
Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")"
Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")"


End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

This will put sums on all but the last set of data. Sorry its not a complete
solution.

Sub InsertRows()

StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
x = StartRow
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")"
Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")"
Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")"
i = i + 1
x = i
End If
i = i + 1
Wend
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ArcticWolf" wrote:

Hi,

I have some VB code (an answer from another question) written by vezerid
which I found very useful and have used in my sheet.

On the extra row that is being created - is it possible to sum the cells
above (of
certain columns) please?
I've used the VB below to put an extra row in after every change in team
name within my organisation (column A). Some teams have 10 people in, while
others have 100+ so putting this blank row automatically is very useful to
separate.
I then go through the sheet and manually sum the columns required (T, U and
BK)
for each team. I've tried to amend the code myself but I can't seem
to get it to work properly, as some of the 'sums' are for the 10 rows above
and then some are for 100.

Is it possible to amend the code to include the sum for these 3 colums
please?

Thanks in advance,

AW


"vezerid" wrote:

Sub InsertRows()

StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
i = i + 1
End If
i = i + 1
Wend
End Sub

Sum different sized (employee) teams
 

Works perfect - thanks for you help Luke :)

ATB,

AW

"Luke M" wrote:

Complete solution. Sorry I missed this earlier.

Sub InsertRows()

StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
x = StartRow
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")"
Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")"
Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")"
i = i + 1

x = i
End If
i = i + 1
Wend

Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")"
Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")"
Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")"


End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

This will put sums on all but the last set of data. Sorry its not a complete
solution.

Sub InsertRows()

StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
x = StartRow
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")"
Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")"
Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")"
i = i + 1
x = i
End If
i = i + 1
Wend
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ArcticWolf" wrote:

Hi,

I have some VB code (an answer from another question) written by vezerid
which I found very useful and have used in my sheet.

On the extra row that is being created - is it possible to sum the cells
above (of
certain columns) please?
I've used the VB below to put an extra row in after every change in team
name within my organisation (column A). Some teams have 10 people in, while
others have 100+ so putting this blank row automatically is very useful to
separate.
I then go through the sheet and manually sum the columns required (T, U and
BK)
for each team. I've tried to amend the code myself but I can't seem
to get it to work properly, as some of the 'sums' are for the 10 rows above
and then some are for 100.

Is it possible to amend the code to include the sum for these 3 colums
please?

Thanks in advance,

AW


"vezerid" wrote:

Sub InsertRows()

StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
i = i + 1
End If
i = i + 1
Wend
End Sub