تسمية للشيت المرحل

[علي بن علي]

المطلوب كود نسخ الورقه وتسمية الشيت

وهو عند ترحيل الورقه ان يكون للأسم للشهر

ب ١ و ٢

كمثال jan23-1

وعند الترحيل يكون الاسم الجديد

كمثال ٢-jan23

 

اي الشهر يقسم لأسمين فقط

الشرح والتوضيح داخل الملف المرفق

تكون التسميه هكذا 

اضافة ورقة جديدة بتسمية معينة.xlsm

[lionheart]

Remove all the code in ThisWorbook module before executing the following code. Also make the worksheet you desire to copy to be Active

Sub Test()
    Dim ws As Worksheet, sh As Worksheet, sName As String, cnt As Long
    Set ws = ActiveSheet
    cnt = 1
    If InStr(ws.Name, "-") Then
        sName = Left(ws.Name, 3) & Right(Year(Date), 2)
    Else
        MsgBox "Worksheet Name Should Have A Hyphen", vbExclamation: Exit Sub
    End If
    Do While SheetExists(sName & "-" & cnt) = True
        cnt = cnt + 1
    Loop
    If cnt > 12 Then
        MsgBox "The Number of Copies Has Reached Its Limit.", vbExclamation: Exit Sub
    End If
    ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set sh = ActiveSheet
    sh.Name = sName & "-" & cnt
End Sub

Function SheetExists(ByVal sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
        Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    If ws Is Nothing Then SheetExists = False Else SheetExists = True
End Function