This changes months, this should account for each month's length - however it keeps triggering on the first if and I cannot figure out why...
(I.E. for some reason - this code assumes every month is 31 days in length, and it shouldn't).
This is super thrown together and could be done more eloquently, but I don't have a lot of time at the moment, so I figured I'd update you with this, and maybe someone can figure out whats wrong with the if statements and why the first one always triggers... (I tried string, int, all comparisons... something wrong with the or's...)
Anyway,
Sub CopyAndClear()
Dim WBT As Workbook ' This workbook
Set WBT = ThisWorkbook
Dim newSheet As Worksheet ' Sheet being created
Dim copySheet As Worksheet ' Sheet being copied
Set copySheet = WBT.Sheets(1)
Dim previousSheetName As String, previousSheetSplit() As String, monthFound As String, previousDate As Integer, nameHolder As String
Dim monthsArray() As Variant
' Array of the months
monthsArray = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
' Copy sheet
copySheet.Copy After:=WBT.Sheets(Sheets.Count) ' Copies to last sheet
Set newSheet = WBT.Sheets(Sheets.Count) ' Sets variable new sheet
previousSheetSplit() = Split(WBT.Sheets(WBT.Sheets.Count - 1).Name, " ") ' Splits second to last sheets name, if it is the one to be modified?
monthFound = previousSheetSplit(1) ' Second to last sheet's month (assuming it is the most recent one????)
previousDate = previousSheetSplit(0) ' Previous date
' Matches current month to the index in the array of months
Dim i As Long, monthIndex As Long
monthIndex = 0
For i = 0 To 11
If monthFound = monthsArray(i) Then
monthIndex = i
End If
Next i
' Checks if month/day needs to be wrapped for a new month
If monthIndex = 0 Or 2 Or 4 Or 6 Or 7 Or 9 Or 11 Then
If previousDate > 24 Then
If monthIndex = 11 Then
monthIndex = 0
Else
monthIndex = monthIndex + 1
End If
If previousDate = 25 Then
newSheet.Name = 1 & " " & monthsArray(monthIndex)
ElseIf previousDate = 26 Then
newSheet.Name = 2 & " " & monthsArray(monthIndex)
ElseIf previousDate = 27 Then
newSheet.Name = 3 & " " & monthsArray(monthIndex)
ElseIf previousDate = 28 Then
newSheet.Name = 4 & " " & monthsArray(monthIndex)
ElseIf previousDate = 29 Then
newSheet.Name = 5 & " " & monthsArray(monthIndex)
ElseIf previousDate = 30 Then
newSheet.Name = 6 & " " & monthsArray(monthIndex)
ElseIf previousDate = 31 Then
newSheet.Name = 7 & " " & monthsArray(monthIndex)
Else
MsgBox ("Could not assign date appropriately, please manually fix.")
End If
ElseIf previousDate <= 24 Then
newSheet.Name = previousDate + 7 & " " & monthsArray(monthIndex)
End If
ElseIf monthIndex = 3 Or 5 Or 8 Or 10 Then
If previousDate > 23 Then
If monthIndex = 11 Then
monthIndex = 0
Else
monthIndex = monthIndex + 1
End If
If previousDate = 24 Then
newSheet.Name = 1 & " " & monthsArray(monthIndex)
ElseIf previousDate = 25 Then
newSheet.Name = 2 & " " & monthsArray(monthIndex)
ElseIf previousDate = 26 Then
newSheet.Name = 3 & " " & monthsArray(monthIndex)
ElseIf previousDate = 27 Then
newSheet.Name = 4 & " " & monthsArray(monthIndex)
ElseIf previousDate = 28 Then
newSheet.Name = 5 & " " & monthsArray(monthIndex)
ElseIf previousDate = 29 Then
newSheet.Name = 6 & " " & monthsArray(monthIndex)
ElseIf previousDate = 30 Then
newSheet.Name = 7 & " " & monthsArray(monthIndex)
Else
MsgBox ("Could not assign date appropriately, please manually fix.")
End If
ElseIf previousDate <= 23 Then
newSheet.Name = previousDate + 7 & " " & monthsArray(monthIndex)
End If
ElseIf monthIndex = 1 Then
If previousDate > 21 Then
If monthIndex = 11 Then
monthIndex = 0
Else
monthIndex = monthIndex + 1
End If
If previousDate = 22 Then
newSheet.Name = 1 & " " & monthsArray(monthIndex)
ElseIf previousDate = 23 Then
newSheet.Name = 2 & " " & monthsArray(monthIndex)
ElseIf previousDate = 24 Then
newSheet.Name = 3 & " " & monthsArray(monthIndex)
ElseIf previousDate = 25 Then
newSheet.Name = 4 & " " & monthsArray(monthIndex)
ElseIf previousDate = 26 Then
newSheet.Name = 5 & " " & monthsArray(monthIndex)
ElseIf previousDate = 27 Then
newSheet.Name = 6 & " " & monthsArray(monthIndex)
ElseIf previousDate = 28 Then
newSheet.Name = 7 & " " & monthsArray(monthIndex)
Else
MsgBox ("Could not assign date appropriately, please manually fix.")
End If
ElseIf previousDate <= 21 Then
newSheet.Name = previousDate + 7 & " " & monthsArray(monthIndex)
End If
End If
'Clears contents of original sheet (if this is the right range? modify if needed)
copySheet.Range("Q8:AB25").ClearContents
End Sub