AndreB
09-09-2021, 11:55 PM
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.
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.