Option Explicit
Option Compare Text
Sub TheSheetCreator()
Dim WS As Worksheet
Dim WSNameOld As String
Dim WSNameNew As String
Dim WSActive As Byte
Dim IncrementNew As Byte
Dim IncrementOld As Byte
WSNameOld = Format(Range('B1'), 'mmm yy')
WSNameNew = Format(Range('D1'), 'mmm yy')
If Not IsDate(WSNameOld) Or Not IsDate(WSNameNew) Then
MsgBox 'Pas de date valide sur la feuille active en B1 ou D1', vbCritical
Exit Sub
End If
For Each WS In ThisWorkbook.Worksheets
If CheckWSName(CStr(WS.Name)) = WSNameOld Then
IncrementOld = IncrementOld + Val(Right(CStr(WS.Name), 1))
' IncrementOld = IIf(Val(Right(WS.Name, 1)) = IncrementOld, IncrementOld = IncrementOld + Val(Right(WS.Name, 1)), IncrementOld)
End If
If CheckWSName(CStr(WS.Name)) = WSNameNew Then
IncrementNew = IncrementNew + Val(Right(CStr(WS.Name), 1))
' IncrementNew = IIf(Val(Right(WS.Name, 1)) = IncrementNew, IncrementNew = IncrementNew + Val(Right(WS.Name, 1)), IncrementNew)
End If
Next
With ActiveSheet
.Name = Format(WSNameOld, 'mmm yy') & ' V-' & IncrementOld
.Copy after:=Worksheets(Worksheets.Count)
End With
With ActiveSheet
.Name = Format(WSNameNew, 'mmm yy') & ' V-' & IncrementNew
.Range('C4, B1').ClearContents
End With
End Sub
Function CheckWSName(ByVal WSName As String) As String
CheckWSName = CStr(Left(WSName, InStr(1, WSName, 'V-', 1) - 2))
End Function