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?

 

[Alex Blakenburg]

See if this does what you need. It only works up to number 98 though.

Sub new_report()
    Dim a
    Dim sht As Worksheet
    Dim shtName As String
    Dim NameSuffix As String

    Set sht = Worksheets(Sheets.Count)
    shtName = sht.Name
    
    NameSuffix = Mid(shtName, Len(shtName) - 1, 2)
    If IsNumeric(NameSuffix) Then
        NameSuffix = CInt(NameSuffix) + 1
        shtName = Left(shtName, Len(shtName) - Len(NameSuffix)) & NameSuffix
    ElseIf IsNumeric(Right(NameSuffix, 1)) Then
        NameSuffix = Right(NameSuffix, 1)
        NameSuffix = CInt(NameSuffix) + 1
        shtName = Left(shtName, Len(shtName) - Len(NameSuffix)) & NameSuffix
    Else
        NameSuffix = 1
        shtName = shtName & NameSuffix
    End If
    
    sht.Copy After:=Sheets(Sheets.Count)
    With ActiveSheet.Cells(1).CurrentRegion.Offset(1)
        ActiveSheet.Name = shtName
        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

 

[Hasson]

your updtaing works perfectly

It only works up to number 98 though.

is it rule in EXCEL?
just curiosity, can I change based on current month and word?
like this "REPORT MONTH OF " & " " & Format(Date, "mmm")
for instance REPORT MONTH OF DECEMBER

 

[Alex Blakenburg]

is it rule in EXCEL?

If you are refering to my reference to 98 (should be 99), no that's not a rule. It is just that I have only catered for 2 digits so it maxes out at 99.

just curiosity, can I change based on current month and word?
like this "REPORT MONTH OF " & " " & Format(Date, "mmm")
for instance REPORT MONTH OF DECEMBER

If you use words you would need to very clear on what you want.
The line of code you have would always name it using the current month, since you are using Date and is not incrementing it based on the last sheet in the workbook.
Also "mmm" would give you Dec not DECEMBER. ("mmmm" would give you December)
If you want to use the previous sheet which is the subject of this thread, we would need to code for increasing the Month Name (for month no the existing code would work).
Also what do you want to do after December ? Just January ? You might already have the previous January in your workbook.

 

[Hasson]

based on last comment if you think you will create new code to achieve it just forget it . I said curiosity

I thought it could mod some lines ,sorry!!

Also what do you want to do after December ? Just January ? You might already have the previous January in your workbook.

in reality , I want showing message box "you should create new file" and don't add any new sheet . this should happen based on last sheet name REPORT OF MONTH DECEMBER

in other meaning , file should from JANUARY to DECEMBER but the month sheets start from third sheet because I have others sheets before it.
thanks again

 

[Alex Blakenburg]

Give this a try if you want to increment the Month Name:

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
        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

 

[Hasson]

works except one thing when reach NOVEMBER month doesn't add DECEMBER month and shows error the is name is already existed , try another name
despite there is no sheet name DECEMBER before I run the code !

 

[Alex Blakenburg]

I have logged out for the night.
When it errors out, in the immediate box paste the below and hit enter.

? NameMain & NameSuffix

If the name makes sense and you believe it doesnt exist look for a hidden sheet with that name.

 

[Hasson]

shows this
JANUARY NOVEMBER

 

[Alex Blakenburg]

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