First of all i want to thank you in advance :)
I don't have any experience in VBA (only coded a few lines in Python and Java a few years ago) but got the task to create a macro that creates a new sheet where are stored the current calendar week + the week 3 weeks after in it. e.g.: Steuerliste_neues_System_KW_25-28.xlsx
Currently I'm not able to get even the current week in it...
This is my current code:
Sub Main()
kw_ermitteln
End Sub
'Kallenderwoche ermitteln
Public Sub kw_ermitteln()
'kw = DINKw("DD.MM.YYYY")
kw = DINKw(Range("I1"))
Dim kwString As String
kwString = CStr(kw)
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=".../Steuerliste_neues_System_KW_" + kwString + ".xlsx"
ActiveWorkbook.Close
End Sub
'Function zur ermittlung der KW
Function DINKw(dat As Date) As Integer
Dim kw As Integer
kw = Int((dat - DateSerial(Year(dat), 1, 1) + ((Weekday(DateSerial(Year(dat), 1, 1)) + 1) _
Mod 7) - 3) / 7) + 1
If kw = 0 Then
kw = DINKw(DateSerial(Year(dat) - 1, 12, 31))
ElseIf kw = 53 And (Weekday(DateSerial(Year(dat), 12, 31)) - 1) Mod 7 <= 3 Then
kw = 1
End If
DINKw = kw
End Function
But my problem with it is, that the new Excel data will just be named Steuerliste_neues_System_KW_.xlsx
I'm ready to learn so thanks again.