Macro to add sequential WS and update summary

[bielerjd]

I have attached a file where i want to click a macro to add a worksheet titled the next week, that is a shell of the previous worksheet. I also want to update the summary sheet when the next worksheet gets populated.

[mdmackillop]

Here's a good start.
[vba]
Option Explicit
Sub NewWeek()
Dim Sh As Worksheet, Num As Long, TAS As Worksheet, ws As Worksheet

Set TAS = ActiveSheet
Application.ScreenUpdating = False
Num = Split(Sheets(3).Name)(UBound(Split(Sheets(3).Name)))
Sheets("TPlate").Visible = True
Sheets("TPlate").Copy after:=Sheets(2)
Set ws = ActiveSheet
Sheets("TPlate").Visible = False
ws.Name = "WEEK " & Num + 1
ws.Range("B32") = Sheets("Week " & Num).Range("B32") + 7
Application.ScreenUpdating = True
TAS.Activate
Call TASupdate(Num, TAS, ws)
End Sub
Sub TASupdate(Num As Long, TAS As Worksheet, ws As Worksheet)
Dim Rng As Range, i As Long, col As Long
col = TAS.Rows(1).Find(What:="Week " & Num, LookAt:=xlWhole, LookIn:=xlFormulas).Column
Columns(col).Resize(, 2).Copy TAS.Cells(1, col + 2)
Set Rng = Columns(col + 2).Resize(, 1)
Application.CutCopyMode = False
Rng.Select
Selection.Replace What:="Week " & Num, Replacement:="Week " & Num + 1, LookAt:=xlPart, MatchCase:=False
TAS.Cells(3, col + 2) = ws.Range("B32")
col = col + 3
If Num >= 3 Then
For i = 2 To 79 Step 11
TAS.Range("B" & i).FormulaR1C1 = "=(R[9]C" & col - 4 & "+R[9]C" & col - 2 & "+R[9]C" & col & ")/3"
Next
TAS.Range("B92").FormulaR1C1 = "=(RC" & col - 4 & "+RC" & col - 2 & "+RC" & col & ")/3"
End If

TAS.Range("A1").Select
End Sub

[/vba]

[bielerjd]

Thank you, I think i am getting there. One thing i am trying to do on the summary page is when I add a sheet, for example week 2, then week 1 on the summary page would move right and week 2 would take the place of week one. I want the 3 week averages to stay in their current position. Thanks for any help.

[mdmackillop]

Version 2

[VBA]
Sub TASupdate(Num As Long, TAS As Worksheet, ws As Worksheet)
Dim Rng As Range, i As Long, col As Long
Columns("C").Copy
Range("C1").Insert Shift:=xlToRight
Columns("C").Select
Selection.Replace What:="Week " & Num, Replacement:="Week " & Num + 1, LookAt:=xlPart, MatchCase:=False
Selection.Replace What:="!B", Replacement:="!D", LookAt:=xlPart, MatchCase:=False
TAS.Cells(3, 3) = ws.Range("B32") + 1
For i = 2 To 79 Step 11
TAS.Range("B" & i).FormulaR1C1 = "=(R[9]C" & 4 & "+R[9]C" & 6 & "+R[9]C" & 8 & ")/3"
Next
TAS.Range("B92").FormulaR1C1 = "=(RC" & 4 & "+RC" & 6 & "+RC" & 8 & ")/3"
TAS.Range("A1").Select
End Sub

[/VBA]