Macro Generate Numbers in Sequential Order

[Rem0ram]

Hi Excelions

I have briefed my requirement in excel.

Kindly have a look and help to generate the file.

Best
Ram

[Rem0ram]

Hi

Can someone advise how to start with this,...??

Best
Rem0

[elayaz]

Hi,

I am not sure whether i understood your needs. But i don't see any need of a macro here..

Just select the cell (a2) and drag down, excel will automatically fill the series TP2000001,TP2000002 till the range you need. same applies to the rest also.

And to lock the cells, select the range you want to lock and right click -->format cells -->protection tab--->click the locked option box..

Now, excel home tab -->format--->protect sheet--> give a password.

Every time you need to add/edit the column, you need unlock with the given password.

Hope this helps..

[jindon]

Bump in such short time?

Sub test()
    Dim ws As Worksheet, num As Long
    num = Abs(Application.InputBox("How many?", Type:=1))
    If num < 2 Then Exit Sub
    For Each ws In Worksheets
        With ws.Range("a" & Rows.Count).End(xlUp)
            .AutoFill .Resize(num + 1)
        End With
    Next
End Sub

[Rem0ram]

Hi Jindon

Thats! perfect and thanks for that....

However, i don't want to generate number for all tab at the same time, if possible can also include the sheetname in the prompt to which i want to generate numbers?

Also i want to protect the column A till the row the value got generated.

Best
Rem0

[Rem0ram]

Hi Elayaz

The problem is this file will be used by a team and should not be able to edit the already generated numbers.

The users should only be able to generate numbers as many as they want in each sheet each time they generate the numbers the protection area should extend the protected area.

Best
Rem0

[elayaz]

Understood. In that case, you will definitely need a macro

[jindon]

Unlock the cell that you don't want to Lock first then run the code

Sub test()
    Dim ws As String, num As Long
    ws = InputBox("Sheet name")
    If (ws = "") + (Not IsSheetExists(ws)) Then Exit Sub
    num = Abs(Application.InputBox("How many?", Type:=1))
    If num < 2 Then Exit Sub
    With Sheets(ws).Range("a" & Rows.Count).End(xlUp)
        .Parent.Unprotect
        .AutoFill .Resize(num + 1)
        .Resize(num + 1).Locked = True
        .Parent.Protect
    End With
End Sub

Function IsSheetExists(ByVal ws As String) As Boolean
    On Error Resume Next
    IsSheetExists = Len(Sheets(ws).Name)
    On Error GoTo 0
End Function

[Rem0ram]

Perfect!

Thanks a lot!

Best
Rem0

[Rem0ram]

HI Jindon

Can we also capture the creation date in column B?

Best
Rem0

[jindon]

Change

        .Resize(num + 1).Locked = True

to

        With .Resize(num + 1, 2)
            .Columns(2).Value = Date
            .Locked = True
        End With

[Rem0ram]

Hi Jindon

Can we get the generated IDs in a new work book after adding them?

Best
Rem0

[Rem0ram]

Hi

Further to the solution provided by Jindon, have a bug in the macro.

Can some one look in to it?

The Timestamp in the column 2 udpates the last row of the previous created sequential number.

Best
Rem0

[Rem0ram]

Hi

Can anyone look into my query and advise me?

Best
Rem0