Year/Month/week Number VBA

Question

I have a range of dates that i would like to separate, year, month and week number of those dates into different columns with VBA. For that I have used the following code, in fact I am calculating them cell by cell:

Sub Sortdata()
   Dim WBData As Workbook
   Dim Lastrow As Long
   Dim j as long
   Dim D as Date

    Set WBData = ThisWorkbook
    Lastrow = WBData.Sheets("CDR").Cells(Rows.Count, "A").End(xlUp).row

    For j = 2 To Lastrow
    D = WBData.Sheets("CDR").Cells(j, 5) 'date 
    
    
    WBData.Sheets("CDR").Cells(j, 19) = Year(D)
    WBData.Sheets("CDR").Cells(j, 20) = Month(D)
    WBData.Sheets("CDR").Cells(j, 21) = Application.WorksheetFunction.WeekNum(D)
    Next j
End Sub

But sometimes the last row is very high over 1000 rows and it takes too much times. Do you have any idea How can i improve this code that it can run in short time?

Many Thanks for your help in advance!

Answer 1

I have an idea though Im not entirely sure it works.

Split Lastrow into 8 parts (or less). Have 8 Separate loops run and have them all called by one single sub so they run simultaneously. You code it one time and then copy paste the code into 8 different modules. Vba is single threaded but some user said subs can run simultaneously if the subs are in different modules. So basically one would run 1 to 125 the other 126 to 250 etc.

Ive never tried it so i dont know if it works.