Excel VBA - Numbering

Please upload a sample for efficiency.

assuming that you have a date in the data set then you can do this:

Sub CalcColB()
    Dim x As Integer
      For x = 2 To Cells(Rows.Count, "A").End(xlUp).Row
       
        If Month(Cells(x, 6).Value) = 1 Then ' if Jan then
            Cells(x, 7).Value = ""
        ElseIf Month(Cells(x, 6).Value) = 2 Then ' if Feb then
            Cells(x, 7).Value = "BB001001"
        ElseIf Month(Cells(x, 6).Value) = 3 Then ' if Mar then
            Cells(x, 7).Value = "BB001002"
        Else
        Cells(x, 7).Value = ""
        End If
    Next x
     
End Sub

and so on....

ORDA-Agency.II.XLSDear All,

In the attached file, he macro "New_Month",  I'm trying to get the last number for March at Cell B17 (II01653) and add 1 there(should be II01654) which should be the 1st begining number for April.  
But because there is a "0" between the Alpha and the numeric part of the number, it does not work in my marco the the result become "II1654" instead of "II01654".

do you have an alterative solution for me.

thanks

Desmond

Below please find my marco

 Application.ScreenUpdating = False
    Range("B9").Select
    Selection.End(xlDown).Select
    Selection.Copy
    Range("P2").Select
    ActiveSheet.Paste
    Selection.Font.ColorIndex = 2
    Dim CurSheet As Worksheet
    Set CurSheet = ActiveSheet
    ActiveSheet.Copy Before:=Sheets(1)
    Range("A8:M8").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Application.Goto Reference:="R8C3"
    Range("B9:I9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("P1").Select
    Selection.Copy
    Range("A9").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Selection.Font.ColorIndex = 0
    Selection.Style = "Comma"
    Range("J9:M9").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Selection.Font.ColorIndex = 0
    Selection.Style = "Comma"
    Application.Goto Reference:="R9C5"
    Application.Goto Reference:="R3C2"
    Selection.ClearContents
    ActiveCell = InputBox("Input New Month (MMM YYYY)")
    Dim ProdDate As String
    ProdDate = Format(Cells(3, 2), "YYMM")
    ActiveSheet.Name = Cells(1, 15) & ProdDate
    Range("P4").Select
    Selection.Copy
    Range("B9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Font.ColorIndex = 0
    Application.ScreenUpdating = True
    Application.Goto Reference:="R9C2"

I have also modified your code to remove redundant statements generated by the macro recorder. See if it works the same.

Sub New_Month()
    Application.ScreenUpdating = False
    Range("B9").End(xlDown).Copy Range("P2")
    Range("P2").Font.ColorIndex = 2
    Dim CurSheet As Worksheet
    Set CurSheet = ActiveSheet
    ActiveSheet.Copy Before:=Sheets(1)
    Range("A8:M8").AutoFilter
    Range("A8:M8").AutoFilter
    Range("B9:I" & Range("B9").End(xlDown).Row).ClearContents
    Range("P1").Copy Range("A9:A" & Range("A9").End(xlDown).Row)
    Range("A9:A" & Range("A9").End(xlDown).Row).Font.ColorIndex = 0
    Range("A9:A" & Range("A9").End(xlDown).Row).Style = "Comma"
    Range("J9:M9").Copy Range("J9:M" & Range("J9").End(xlDown).Row)
    Range("J9:M" & Range("J9").End(xlDown).Row).Font.ColorIndex = 0
    Range("J9:M" & Range("J9").End(xlDown).Row).Style = "Comma"
    Range("B3").ClearContents
    Range("B3") = InputBox("Input New Month (MMM YYYY)")
    Dim ProdDate As String
    ProdDate = Format(Cells(3, 2), "YYMM")
    ActiveSheet.Name = Cells(1, 15) & ProdDate
    Range("P4").Copy
    Range("B9").PasteSpecial Paste:=xlPasteValues
    Range("B9").Font.ColorIndex = 0
    Application.ScreenUpdating = True
End Sub

Select allOpen in new window

thanks so much

cheers

Dear ssaqibh

Sorry, it did not add "1" to the count at the next worksheek.


demsond

Are you talking about the accepted answer or the modified code?

I just realise that the new formula has solve my "0" problem in my Alphanumeric number. But I need to add 1 count to the serial number when I go to the new month.
Hope I have explain the problem clearly.

I started with the II-1203 sheet which has II01653
I ran the macro and built the II-1204 sheet.
The first number in the new II-1204 sheet was II01654

Am I missing something?

Sorry, I have mistakenly place the formula on P3 instead of P4, that's why it doesn't work
I've fixed it now,

thanks so much