RattlingCarp3048
Board Regular
- Joined
- Jan 12, 2022
- Messages
- 202
- Office Version
- 365
- Platform
- Windows
This is a snip it from a VBA code we are using. Under "Define each variable in date" is there a way to automate the year definition so we do not have to manually change it at the start of the new year? I tried "=TEXT(TODAY(),""YYYY"")" but that errored on me.
Sub Create_Tabs()
Dim sht As Worksheet
Dim n As Long, y As Long, x
Dim i As Long
Application.ScreenUpdating = False
'Set sht = Master Packing List
Set sht = Sheets("Master Packing List")
''Define each variable in date
'Define Year
y = 2022 'year
'Define Month
x = Application.InputBox("Enter Month Number" & vbNewLine & "January = 1" & vbNewLine & "February = 2" & vbNewLine & "etc...", "Create Daily Tabs")
'Msgbx if month entered is invalid
If x < 0 Or x > 12 Or Not IsNumeric(x) Or x = False Then
MsgBox "Wrong entry"
Exit Sub
End If
'Define last day of month
n = Format(WorksheetFunction.EoMonth(DateSerial(y, x, 1), "0"), "d")
Sub Create_Tabs()
Dim sht As Worksheet
Dim n As Long, y As Long, x
Dim i As Long
Application.ScreenUpdating = False
'Set sht = Master Packing List
Set sht = Sheets("Master Packing List")
''Define each variable in date
'Define Year
y = 2022 'year
'Define Month
x = Application.InputBox("Enter Month Number" & vbNewLine & "January = 1" & vbNewLine & "February = 2" & vbNewLine & "etc...", "Create Daily Tabs")
'Msgbx if month entered is invalid
If x < 0 Or x > 12 Or Not IsNumeric(x) Or x = False Then
MsgBox "Wrong entry"
Exit Sub
End If
'Define last day of month
n = Format(WorksheetFunction.EoMonth(DateSerial(y, x, 1), "0"), "d")