Total a column and repeat headings

[marcidee]

Please can someone help me with a script - I have a sheet that has a blank row every time a new name appears in column A

What i would like is to:

Place a total of the figures in column G - total in column F (last row)
Repeat the headings from row 1 on each blank line

Example of end result below

Thank you for your help


[TABLE="width: 815"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Timesheet[/TD]
[TD]P/E Date[/TD]
[TD]Company[/TD]
[TD]Time worked[/TD]
[TD]Pay Rate[/TD]
[TD]Total Pay[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abimbola Dunsin (Dunsin) B[/TD]
[TD]1[/TD]
[TD]Thu 08 Aug 2019[/TD]
[TD]Peter Howes[/TD]
[TD="align: right"]0.75[/TD]
[TD]9.00[/TD]
[TD="align: right"]6.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abimbola Dunsin (Dunsin) B[/TD]
[TD]1[/TD]
[TD]Fri 09 Aug 2019[/TD]
[TD]Peter Howes[/TD]
[TD="align: right"]0.5[/TD]
[TD]9.00[/TD]
[TD="align: right"]4.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abimbola Dunsin (Dunsin) B[/TD]
[TD]1[/TD]
[TD]Sat 10 Aug 2019[/TD]
[TD]Phillip Mercer[/TD]
[TD="align: right"]0.25[/TD]
[TD]9.00[/TD]
[TD="align: right"]2.25[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Timesheet[/TD]
[TD]P/E Date[/TD]
[TD]Company[/TD]
[TD]Time worked[/TD]
[TD]Pay Rate[/TD]
[TD]Total Pay[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Afaque Solangi[/TD]
[TD]1[/TD]
[TD]Mon 12 Aug 2019[/TD]
[TD]Mohsen Taheri[/TD]
[TD="align: right"]7[/TD]
[TD]9.00[/TD]
[TD="align: right"]63[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Afaque Solangi[/TD]
[TD]1[/TD]
[TD]Tue 13 Aug 2019[/TD]
[TD]Mohsen Taheri[/TD]
[TD="align: right"]7[/TD]
[TD]9.00[/TD]
[TD="align: right"]63[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Afaque Solangi[/TD]
[TD]1[/TD]
[TD]Wed 14 Aug 2019[/TD]
[TD]Mohsen Taheri[/TD]
[TD="align: right"]7[/TD]
[TD]9.00[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]189[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Timesheet[/TD]
[TD]P/E Date[/TD]
[TD]Company[/TD]
[TD]Time worked[/TD]
[TD]Pay Rate[/TD]
[TD]Total Pay[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amalia Gatou B[/TD]
[TD]1[/TD]
[TD]Thu 15 Aug 2019[/TD]
[TD]Amanda King[/TD]
[TD="align: right"]1[/TD]
[TD]9.00[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amalia Gatou B[/TD]
[TD]1[/TD]
[TD]Fri 16 Aug 2019[/TD]
[TD]Amanda King[/TD]
[TD="align: right"]0.25[/TD]
[TD]9.00[/TD]
[TD="align: right"]2.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amalia Gatou B[/TD]
[TD]1[/TD]
[TD]Sat 17 Aug 2019[/TD]
[TD]Amanda King[/TD]
[TD="align: right"]0.25[/TD]
[TD]9.00[/TD]
[TD="align: right"]2.25[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
</tbody>[/TABLE]

I had posted this question on another forum however I did not find a solution - the link to this question is below:

https://www.ozgrid.com/forum/forum/h...es-in-column-a

The first part of the link is irrelevant as I have been fortunate to be given the solution in this forum by -'fluff' however I am looking for a solution to this question as I require both methods

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG13Aug32
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Range("G:G").SpecialCells(xlCellTypeConstants).Areas
    R(R.Count).Offset(, 1) = Application.Sum(R)
    [COLOR="Navy"]If[/COLOR] R(R.Count).Offset(, 1).Row < lst [COLOR="Navy"]Then[/COLOR]
        Range("A1:G1").Copy R(R.Count).Offset(1, -6)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[marcidee]

That works, perfect - your help is very much appreciated.

Thank you

 

[MickG]

You're welcome

 

[marcidee]

I am now having a problem to get the last part of the solution that you gave me last night working (total in column F and repeating first line) this were working fine - the sheet I was working on has changed slightly - but the only difference I can see is that column D contains no data (I left it like this so Column G remained as G. When running the script it is now not adding the headings or the totals?

I now see the below after running the script

[TABLE="width: 660"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]P/E Date[/TD]
[TD]Client[/TD]
[TD][/TD]
[TD]Time Worked[/TD]
[TD]Pay Rate[/TD]
[TD]Total Pay[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]P/E Date[/TD]
[TD]Client[/TD]
[TD][/TD]
[TD]Time Worked[/TD]
[TD]Pay Rate[/TD]
[TD]Total Pay[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abimbola Dunsin (Dunsin) B[/TD]
[TD]Fri 02 Aug 2019[/TD]
[TD]Constance Rubidge[/TD]
[TD][/TD]
[TD="align: right"]0.75[/TD]
[TD]9.00[/TD]
[TD="align: right"]6.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abimbola Dunsin (Dunsin) B[/TD]
[TD]Sat 03 Aug 2019[/TD]
[TD]Constance Rubidge[/TD]
[TD][/TD]
[TD="align: right"]0.25[/TD]
[TD]9.00[/TD]
[TD="align: right"]2.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abimbola Dunsin (Dunsin) B[/TD]
[TD]Sat 10 Aug 2019[/TD]
[TD]Sheila Rice[/TD]
[TD][/TD]
[TD="align: right"]0.75[/TD]
[TD]9.00[/TD]
[TD="align: right"]6.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abimbola Dunsin (Dunsin) B[/TD]
[TD]Sun 11 Aug 2019[/TD]
[TD]Theresa Darling[/TD]
[TD][/TD]
[TD="align: right"]0.25[/TD]
[TD]9.00[/TD]
[TD="align: right"]2.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Afaque Solangi[/TD]
[TD]Mon 12 Aug 2019[/TD]
[TD]Mohsen Taheri[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD]9.00[/TD]
[TD="align: right"]63.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Afaque Solangi[/TD]
[TD]Tue 13 Aug 2019[/TD]
[TD]Mohsen Taheri[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD]9.00[/TD]
[TD="align: right"]63.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Afaque Solangi[/TD]
[TD]Wed 14 Aug 2019[/TD]
[TD]Mohsen Taheri[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD]9.00[/TD]
[TD="align: right"]63.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amalia Gatou B[/TD]
[TD]Thu 15 Aug 2019[/TD]
[TD]Amanda King[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amalia Gatou B[/TD]
[TD]Fri 16 Aug 2019[/TD]
[TD]Amanda King[/TD]
[TD][/TD]
[TD="align: right"]0.25[/TD]
[TD]9.00[/TD]
[TD="align: right"]2.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amalia Gatou B[/TD]
[TD]Sat 17 Aug 2019[/TD]
[TD]Amanda King[/TD]
[TD][/TD]
[TD="align: right"]0.25[/TD]
[TD]9.00[/TD]
[TD="align: right"]2.25[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

The code works based on the Blank rows , which is what you had in the first instance.
When you run the code the Header in row 1 are added to each blank row and also the totals for each set of data
Once you have run the code, those blank rows are filled with the header and running the code again will not do anything.
I noticed on your previous data there was a "0" in cell "G2", this "0" stopped the otherwise blank row 2 from also filling with the headers.
In the current data, Row 2 have now got headers in it and "H1" seems to have a "0" I think that is because after originally running the code, you removed the previous "0" in "G2" and ran the code again, resulting in what you have now.

NB:- Data Column "D" data should make no difference to the code.
To Run the code again to get a result, you will need to remove the headers Rows where they where previous filled, when the code was first run, and then run the code again.
Good Luck
Regrds Mick

 

[marcidee]

The sheet looks like the below before I am running the script and this is the script I am using

Option Explicit
Sub RunAlladdblank()
Call AddBlankRows
Call MG13Aug32

End Sub






Sub AddBlankRows()
'
Dim iRow As Integer, iCol As Integer
Dim oRng As Range


Set oRng = Range("a1")


iRow = oRng.Row
iCol = oRng.Column


Do
'
If Cells(iRow + 1, iCol) <> Cells(iRow, iCol) Then
Cells(iRow + 1, iCol).EntireRow.Insert Shift:=xlDown
iRow = iRow + 2
Else
iRow = iRow + 1
End If
'
Loop While Not Cells(iRow, iCol).Text = ""
'
End Sub






Sub MG13Aug32()
Dim R As Range, lst As Long
lst = Range("A" & Rows.Count).End(xlUp).Row
For Each R In Range("G:G").SpecialCells(xlCellTypeConstants).Areas
R(R.Count).Offset(, 1) = Application.Sum(R)
If R(R.Count).Offset(, 1).Row < lst Then
Range("A1:G1").Copy R(R.Count).Offset(1, -6)
End If
Next R
End Sub



[TABLE="width: 514"]
<colgroup><col span="2"><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Timesheet[/TD]
[TD]P/E Date[/TD]
[TD]Client[/TD]
[TD]Time Worked[/TD]
[TD]Pay Rate[/TD]
[TD]Total Pay[/TD]
[/TR]
[TR]
[TD]Abimbola Dunsin (Dunsin) B[/TD]
[TD]1[/TD]
[TD]Fri 02 Aug 2019[/TD]
[TD]Constance Rubidge[/TD]
[TD="align: right"]0.75[/TD]
[TD]9.00[/TD]
[TD="align: right"]6.75[/TD]
[/TR]
[TR]
[TD]Abimbola Dunsin (Dunsin) B[/TD]
[TD]1[/TD]
[TD]Fri 02 Aug 2019[/TD]
[TD]Constance Rubidge[/TD]
[TD="align: right"]0.25[/TD]
[TD]9.00[/TD]
[TD="align: right"]2.25[/TD]
[/TR]
[TR]
[TD]Abimbola Dunsin (Dunsin) B[/TD]
[TD]1[/TD]
[TD]Fri 02 Aug 2019[/TD]
[TD]Isobella Clark,[/TD]
[TD="align: right"]0.5[/TD]
[TD]9.00[/TD]
[TD="align: right"]4.50[/TD]
[/TR]
[TR]
[TD]Afaque Solangi[/TD]
[TD]1[/TD]
[TD]Mon 29 Jul 2019[/TD]
[TD]Mohsen Taheri[/TD]
[TD="align: right"]7[/TD]
[TD]9.00[/TD]
[TD="align: right"]63.00[/TD]
[/TR]
[TR]
[TD]Afaque Solangi[/TD]
[TD]1[/TD]
[TD]Wed 31 Jul 2019[/TD]
[TD]Mohsen Taheri[/TD]
[TD="align: right"]7[/TD]
[TD]9.00[/TD]
[TD="align: right"]63.00[/TD]
[/TR]
[TR]
[TD]Afaque Solangi[/TD]
[TD]1[/TD]
[TD]Fri 02 Aug 2019[/TD]
[TD]Mohsen Taheri[/TD]
[TD="align: right"]7[/TD]
[TD]9.00[/TD]
[TD="align: right"]63.00[/TD]
[/TR]
[TR]
[TD]Amalia Gatou B[/TD]
[TD]1[/TD]
[TD]Wed 31 Jul 2019[/TD]
[TD]Amanda King[/TD]
[TD="align: right"]1[/TD]
[TD]9.00[/TD]
[TD="align: right"]9.00[/TD]
[/TR]
[TR]
[TD]Amalia Gatou B[/TD]
[TD]1[/TD]
[TD]Wed 31 Jul 2019[/TD]
[TD]Amanda King[/TD]
[TD="align: right"]0.25[/TD]
[TD]9.00[/TD]
[TD="align: right"]2.25[/TD]
[/TR]
[TR]
[TD]Ana Sofia Alves De Almieda (Sofia) Team Leader[/TD]
[TD]1[/TD]
[TD]Mon 29 Jul 2019[/TD]
[TD]Beryl Jospehine[/TD]
[TD="align: right"]0.75[/TD]
[TD]9.00[/TD]
[TD="align: right"]6.75[/TD]
[/TR]
[TR]
[TD]Ana Sofia Alves De Almieda (Sofia) Team Leader[/TD]
[TD]1[/TD]
[TD]Mon 29 Jul 2019[/TD]
[TD]Beryl Jospehine[/TD]
[TD="align: right"]0.5[/TD]
[TD]9.00[/TD]
[TD="align: right"]4.50[/TD]
[/TR]
[TR]
[TD]Ana Sofia Alves De Almieda (Sofia) Team Leader[/TD]
[TD]1[/TD]
[TD]Mon 29 Jul 2019[/TD]
[TD]Isobella Clark,[/TD]
[TD="align: right"]0.5[/TD]
[TD]9.00[/TD]
[TD="align: right"]4.50

[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

That seems to work OK for me except it places a Extra header in rows 2.
if you change :- iRow = 2 That should stop that!!

 

[marcidee]

I am at a loss as it doesn't work on mine?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG14Aug44
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
Lst = Range("A" & Rows.Count).End(xlUp).Row

[COLOR="Navy"]For[/COLOR] n = Lst To 3 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]If[/COLOR] Cells(n, 1) <> Cells(n - 1, 1) [COLOR="Navy"]Then[/COLOR]
        Cells(n, 1).EntireRow.Insert shift:=xlDown
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n

Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Range("G:G").SpecialCells(xlCellTypeConstants).Areas
        R(R.Count).Offset(, 1) = Application.Sum(R)
        [COLOR="Navy"]If[/COLOR] R(R.Count).Offset(, 1).Row < Lst [COLOR="Navy"]Then[/COLOR]
            Range("A1:G1").Copy R(R.Count).Offset(1, -6)
        [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick