VBA code to generate dates in different sheets

[naveen_jaina]

Hello

I'm new to learning the VBA macros, and not able to figure out how to resolve one issue here.

I need to create a macro that'd automatically add consecutive dates to different sheets in two weeks of increments (image of the test sheet attached here, not able to get xl2bb to work - sorry). Here's the code that I have wrote so far:

-------------------------------------------
Sub loopdate()

Dim s As Integer
For s = 1 To 9
Sheets(s).Select
Dim d As Date
For d = "09/20/2021" To "10/03/2021"

Dim c As range
For Each c In range("C33")
c.Value = d
ActiveCell.Offset(0, 1).Select
d = d + 1
Next
Next
Next
End Sub
-------------------------------------------
As you can probably see, I need to add two weeks of dates (09/20/2021" To "10/03/2021) in the range ("C33") in Sheet1, and then next two weeks of dates (10/04/2021 to 10/17/2021) in the same range ("C33") in Sheet2 and next two weeks in Sheet3 and so on.

Now, when I run this above code, it works in sheet1, but also adds the first two weeks of dates in Sheet2, Sheet3 and so on. How do I correct it to move to the next two weeks in the second week, please? Hope I am making some sense here.

Thank you so much in advance for all your help.

Nav

 

[naveen_jaina]

Looks like I just figured out the solution and it seems to be working as intended. Sorry. :P

 

[Alex Blakenburg]

In case you wanted another option you can try this.

PS: you might want to consider how you are addressing the sheets. Your method above is going to to use the sheets from left to right. eg the furthest to the left will be 1. So if you put your summary pages at the beginning of your workbook you will need to change your numbering.

Sub SetUpDates()
    
    Dim s As Integer
    Dim outSht As Worksheet
    Dim outDate As Date
    Dim outDateEnd As Date
    Dim cycleDays As Long
    
    outDate = DateSerial(2021, 9, 20)
    cycleDays = 14
    
    For s = 1 To 9
        Set outSht = Worksheets("Sheet" & s)
        
        outDateEnd = outDate + cycleDays - 1
        
        With outSht
            .Range("C3").Value = outDate
            .Range("C3").DataSeries xlRows, xlChronological, xlDay, 1, outDateEnd
        End With
        outDate = outDateEnd + 1
    
        outSht.Range("c3").Resize(1, cycleDays - 1).EntireColumn.AutoFit
        
    Next s
    
End Sub

 

[naveen_jaina]

Thank you for your input, Alex. Really appreciate your time and suggestion. I'll definitely try your method as well to learn more about it.

In case you wanted another option you can try this.

PS: you might want to consider how you are addressing the sheets. Your method above is going to to use the sheets from left to right. eg the furthest to the left will be 1. So if you put your summary pages at the beginning of your workbook you will need to change your numbering.

 

[Alex Blakenburg]

Thank you for your input, Alex. Really appreciate your time and suggestion. I'll definitely try your method as well to learn more about it.

Thanks for the feedback, let me know if you need help with it.
Ideally you want to avoid "Select" & "Activate" statements wherever possible, they slow the code down and it is also easy to get confused as where you are in the spreadsheet.