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?

 

[My Aswer Is This]

You said:
and each time I batch duplicate

define batch duplicate

Not sure what that means.

Does it mean your making new copies of the sheets?

 

[SereneSea]

You said:
and each time I batch duplicate

define batch duplicate

Not sure what that means.

Does it mean your making new copies of the sheets?

Yeah. I have a template and then I use the following code to duplicate it 52 times and it automatically renames the sheets. It would be nice while doing this to also have C2 for each sheet go up in date by 7 days.

Sub Copypastesheets()
    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
    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.Name = "Week-" & i
           Next
    xActiveSheet.Activate
    Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Assuming you have a proper date in Range("C2") of the sheet you want copied
Try this:

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]

A Proper date would be like:
1/3/2022

Not like in your example where you showed:
C2 is Jan 3 2022

 

[SereneSea]

Thank you, I also changed the date to that format. What the code is doing is adding 7 days to the next worksheet then just copies. So Week 1 i have 12/27/2021 then Week 2 does get changed to 1/3/2022 (7 is added) but the rest of the sheets from here on are all 1/3/2022. Is there a way to keep adding 7 as it gets copied?

 

[My Aswer Is This]

I test all my scripts and the script worked for me.
If you tell the script to make 20 copies
All 20 sheets that are added get the same value in range("C2")
But now this only happens with the 20 new sheets not all the sheets in the workbook

 

[My Aswer Is This]

I just went back and tested my script again and in each new sheet the date in Range("C2") get changed like you asked for The Date in Range("C2") of the sheet we are copying

Now I do think the sheet we are copying should have a Name like "Master"
But your using active sheet for the name of the sheet we are copying.

 

[SereneSea]

Interesting, I renamed my sheet to "Template" and still have the same bug : "Week-2" gets the seven days and then the rest of the days are just copies of Week-2

 

[My Aswer Is This]

Show me your new script:

So if the sheet we want to copy is named "Master" and in Range("C2") you have 1/7/2022

Then you want 1/14/2022 put into Range("C2") of all the new sheets we make is that correct?