VBA Changing same cell in multiple sheets while adding a number each time

[SereneSea]

I am looking for a VBA code that will change multiple sheets at once while adding 7 each time.
My sheets are all labelled Week-1, Week 2 up to Week 53 and each time I batch duplicate I would like to be able to change my Cell C2 to add 7 days.

Ie. In the Week 2 sheet, C2 is Jan 3 2022, I would like Week-3 Sheet to be automatically labelled Jan 10 2022 and so on.

Is there an easy code to do this?

 

[SereneSea]

I would like 1/14/2022 in the next sheet, then the next sheet 1/21/2022 then the next sheet to have 1/28/2022 and so on. Basically each new sheet to have a new Monday start is C2.

Sub Copypastesheets()
    'Modified  2/2/2022  8:47:28 PM  EST
    Dim i As Long
    Dim xNumber As Integer
    Dim xName As String
    Dim xActiveSheet As Worksheet
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xActiveSheet = ActiveSheet
    Dim r As String
    r = ActiveSheet.Range("C2").value + 7
    xNumber = InputBox("Enter number of times to copy the current sheet")
    For i = 1 To xNumber
        xName = ActiveSheet.Name
        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
        ActiveSheet.Range("C2").value = r
        ActiveSheet.Name = "Week-" & i
        
        
           Next
    xActiveSheet.Activate
    Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Sorry I was confused
Try this:

Sub Copypastesheets()
    'Modified  2/3/2022  12:21:21 PM  EST
    Dim i As Long
    Dim xNumber As Integer
    Dim xName As String
    Dim xActiveSheet As Worksheet
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xActiveSheet = ActiveSheet
    Dim r As Long
    r = ActiveSheet.Range("C2").Value
    xNumber = InputBox("Enter number of times to copy the current sheet")
    For i = 1 To xNumber
        r = r + 7
        xName = ActiveSheet.Name
        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
        ActiveSheet.Range("C2").Value = r
        ActiveSheet.Name = "Week-" & i
        
        
           Next
    xActiveSheet.Activate
    Application.ScreenUpdating = True
End Sub

 

[SereneSea]

YES! Thank you so so much!!! This saves me so much time!

 

[My Aswer Is This]

YES! Thank you so so much!!! This saves me so much time!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.