Sum Most Recent 13 Weeks

Mar
I've just noticed that you said the most recent 13 weeks are to the left of the totals, but I've done everything based on their being to the right of the totals!
Unless you can reformat your sheet, none of what I gave you is any good!
Celia

Celia,

You are awesome! Thanks for your very competent help; you've saved me!

Mar

Celia,

The most recent weeks are to the left of the total column; Data begins in row 5. I would prefer to use a macro to hide the 14th column to the left of total and, yes, (great idea) also add a new blank column for the most recent week's data.
Thank you sooo much for your help.

Mar

Mar
How is your spreadsheet set up?
Are the most recent weeks' columns on the left of the total column, or on the right, or somewhere else? What is the starting row for your data (I.E. how many header rows)?
The rolling total can be done by a worksheet function or by a macro, but hiding the 14th week column needs to be done manually or by a macro.
Which way do you prefer? If you prefer all to be done by a macro, would you like the macro also to insert a column for the new week?
Celia

Mar
I have assumed that there are row headings in Column A and that the Totals are in Column B. The macro inserts a new column after Column B and puts the sum formula in column B from B5 to the last entry in Column A. You can manually delete or insert columns (other than Columns A & B) and the totals in Column B will always be the sum of the thirteen Columns C to O.

Sub UpdateNewWeek()
Dim ColA As Range, totCol As Range
Dim cell As Range
Set ColA = Range(Range("A5"), _
Range("A65536").End(xlUp))
Set totCol = Range(Range("B5"), _
Range("B65536").End(xlUp))
If Application.CountA(ColA) <> _
Application.CountA(totCol) Then
For Each cell In ColA.Offset(0, 1)
cell.Formula = "=SUM(INDIRECT(" _
& Chr(34) & cell.Offset(0, 1). _
Address(False, False) & ":" & _
cell.Offset(0, 13).Address _
(False, False) & Chr(34) & "))"
Next
Else
ColA.Offset(0, 15).EntireColumn _
.Hidden = True
ColA.Offset(0, 2).EntireColumn.Insert
Set totCol = ColA.Offset(0, 1)
For Each cell In totCol
cell.Formula = "=SUM(INDIRECT(" _
& Chr(34) & cell.Offset(0, 1). _
Address(False, False) & ":" & _
cell.Offset(0, 13).Address _
(False, False) & Chr(34) & "))"
Next
End If
End Sub

When inputting data for the new week, if you add new rows, run the macro again to put the sum formula in Column B for the rows added. It will not insert a new column – it will only insert a new column if the number of records in Column A is the same as the number of entries in Column B. If you run the macro by mistake and insert an unwanted new column, just manually delete the column – but you will also have to manually unhide the "14th" week column although the sum formulas will remain intact whether or not you unhide it.

What are the column headings for each week? The heading for the new week can also be entered by the macro – the code will depend on the heading format. For example :-

If the heading format is for example "Week 1" :-
Range("C3") = "Week " & Mid(Range("D3").Text, InStr(Range("D3").Text, " ") + 1) + 1
(Note: another possible way is to create a custom list and fill the heading either by dragging manually or by the macro)

If the heading is a date :-
Range("C3")=Range("D3")+7

If the Heading is a number :-
Range("C3")=Range("C3") +1

If you use any of this code, put it in the macro between the last Next and the End If (I.E. between the penultimate and the pen-penultimate rows!)

Celia