Dynamic column Add and names

[AndreB]

Dear Gurus,
I have a table in a worksheet in Excel and based on the input dates I insert in another worksheet, I would like to add to the table a given number of columns whose name should be in the format MM-YYYY.

Let me try to give you the full explanation:

Worksheet1 - I insert:

StartDate: jan-2021
EndDate: mar-2021
Duration: 3

Worksheet2 - I have the following table:

Name | Surname | Total |

Now, I would like to add by code to the table in worksheet2 3 columns named: jan-2021, feb-2021, mar-2021.

Here below the code I've written so far:



Private Sub InitializeProject_Click()
Dim ws1, ws2 As Worksheet
Dim dur As Variant
Dim i As Long
Dim table3 As ListObject
Dim stdate, stdate2 As Date


Set ws1 = Worksheets(1)
dur = ws1.Range("C8").Value
stdate = ws1.Range("C6").Value


Set ws2 = Worksheets(2)
Set table3 = ws2.ListObjects("Table3")


For i = 1 To dur
table3.ListColumns.Add


If i = 1 Then
stdate2 = Format(stdate, "mmm-yyyy")
table3.ListColumns("Column1").Name = stdate2
End If


Next i


End Sub


Unfortuntely, the result I get now is made by three columns (and this corresponds to what I want), but with the following names:
1/1/2021, 1/1/2022, 1/1/2023.

Could you please help me to sort this out?

Thanks in advance,
A.

[arnelgp]

Private Sub InitializeProject_Click()
Dim ws1, ws2 As Worksheet
Dim dur As Variant
Dim i As Long
Dim table3 As ListObject
Dim stdate, stdate2 As Date
Dim colCount As Integer
Set ws1 = Worksheets(1)
dur = ws1.Range("C8").Value
stdate = ws1.Range("C6").Value


Set ws2 = Worksheets(2)
Set table3 = ws2.ListObjects("Table3")


With table3
For i = 1 To dur
.ListColumns.Add
colCount = .ListColumns.Count
.ListColumns(colCount).Range(1).Select
Selection.NumberFormat = "mmm\-yyyy"
Selection.Value = DateAdd("m", i - 1, stdate)

Next
End With


End Sub

[AndreB]

Thanks a lot arnelgp,
that works perfectly!

Thanks,
A.

[AndreB]

Dear arnelgp,
I've tried to replicate the same logic on another worksheet/table.

It seems working for the first loop of "For" condition, but at the end it is retrieving me an error.
Please see the code below:



Private Sub InitializeProject_Click()


Dim ws1, ws2, ws3, ws4 As Worksheet
Dim dur, dur2 As Variant
Dim i, j As Long
Dim table3, table4 As ListObject
Dim stdate, stdate2 As Date
Dim colCount, colCount1 As Integer
Set ws1 = Worksheets(1)
dur = ws1.Range("C8").Value
stdate = ws1.Range("C6").Value


'Tab Resource


Set ws2 = Worksheets(2)


ws1.Range("B12:B31").Copy
ws2.Range("B5").PasteSpecial Paste:=xlPasteAll, Transpose:=False
Application.CutCopyMode = False


'Tab Effort


Set ws3 = Worksheets(3)
Set table3 = ws3.ListObjects("EffortResources")


With table3
For i = 1 To dur
.ListColumns.Add
colCount = .ListColumns.Count
.ListColumns(colCount).Range(1).Select
Selection.NumberFormat = "mmm\-yyyy"
Selection.Value = DateAdd("m", i - 1, stdate)

Next i
End With


ws1.Range("B12:B31").Copy
ws3.Range("B5").PasteSpecial Paste:=xlPasteAll, Transpose:=False
Application.CutCopyMode = False


ws3.Columns("B:BZ").AutoFit


'Tab Other Costs


Set ws1 = Worksheets(1)
dur2 = ws1.Range("C8").Value
stdate2 = ws1.Range("C6").Value


Set ws4 = Worksheets(4)
Set table4 = ws4.ListObjects("Costs")


With table4
For j = 1 To dur2
.ListColumns.Add
colCount1 = .ListColumns.Count
.ListColumns(colCount1).Range(1).Select
Selection.NumberFormat = "mmm\-yyyy"
Selection.Value = DateAdd("m", j - 1, stdate2)


Next j
End With


End Sub


Thanks in advance for the help,
A.