change code to work correctly(increment numbers of new added sheet)

[Hasson]

Hi

I need fixing this code

Sub new_report()
    Dim a
  
   Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)
    With ActiveSheet.Cells(1).CurrentRegion.Offset(1)
    ActiveSheet.Name = "TEST1" & Sheets.Count - 1
        a = .Value
        .ClearContents
        Cells(2, 1).Resize(UBound(a), 3) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(1, 2, 6))
    End With
 
End Sub

the code add new sheet with copy specific data and the same formatting and incrementing numbers based on sheet number

location of sheet in in third sheet , for instance name the sheet third is TEST1 and based on the code will be TEST4 but what I want TEST2,TEST3,TEST4 consecutively

I need change this line

ActiveSheet.Name = "TEST1" & Sheets.Count - 1

but I no know how
is there any way experts?

 

[Hasson]

The code is expecting a prefix per your example
REPORT OF MONTH DECEMBER

whether use DECEMBER or REPORT OF MONTH DECEMBER it shows the same problem .
it should add up to DECEMBER after DECEMBER shouldn't add.
if youe see the problem from me I will implement for another file and comeback soon what happens for me .

 

[Hasson]

here is the file
TST.xlsm
if it works for you without any error it will very strange!!!!

 

[Alex Blakenburg]

Change the Else part from this:

    Else
        NameSuffix = "JANUARY"
    End If

To this:

    Else
        NameMain = shtName
        NameSuffix = " JANUARY"
    End If

 

[Hasson]

thanks, the problem is still continuing

 

[Alex Blakenburg]

It didn't work on your test file or on your real file ?
And what did it do or not do.

If it didn't work on your test file then send me the full code you are using.
If it didn't work on you real file show me the tabs of your real file.

Here is your test file after I ran it twice.

 

[Hasson]

It didn't work on your test file or on your real file ?

two both files don't work

And what did it do or not do.

as you guided me

If it didn't work on your test file then send me the full code you are using.

Sub new_report_MthName()
    Dim a
    Dim sht As Worksheet
    Dim shtName As String
    Dim NameSuffix As String, NameMain As String
    Dim currMth As Date

    Set sht = Worksheets(Sheets.Count)
    shtName = sht.Name
    
    NameMain = Left(shtName, InStrRev(shtName, " "))
    NameSuffix = Right(shtName, Len(shtName) - InStrRev(shtName, " "))
    
    If UCase(NameSuffix) = "DECEMBER" Then
        MsgBox "you should create new file"
        Exit Sub
    End If
    
    On Error Resume Next
    currMth = DateValue("1-" & NameSuffix)
    If Err = 0 Then
        NameSuffix = UCase(MonthName((Month(currMth) + 1) Mod 12))
    
        Else
        NameMain = shtName
        NameSuffix = " JANUARY"
    End If
    On Error GoTo 0

    sht.Copy After:=Sheets(Sheets.Count)
    With ActiveSheet.Cells(1).CurrentRegion.Offset(1)
        ActiveSheet.Name = NameMain & NameSuffix
        a = .Value
        .ClearContents
        Cells(2, 1).Resize(UBound(a), 3) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(1, 2, 6))
    End With
 
End Sub

Here is your test file after I ran it twice.

but I need see the last added sheet for DECEMBER month because my problem is when add after NOVEMBER month will show the error .

 

[Alex Blakenburg]

OK can you replace the current If statement section with this:

    If Err = 0 Then
        NameSuffix = UCase(MonthName(Month(currMth) + 1))
    Else
        NameMain = shtName
        NameSuffix = " JANUARY"
    End If

 

[Hasson]

finally it works and thanks again

last thing I want to use your code for others PC , but the problem is the language is not english , so will occures problem and gives error . I searched for it in the internet could enforce adding sheets by english, even if the language is not english in PC by put the months in array instead of change setting language in PC
many times like this

dim am

am = [{"JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", "AUGUST", "SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER"}]

but I no know how add some procedures in your code to achive that , do you have any idea ?