auto incremental numbers

[j4ymf]

hello all

can you please tell me how to auto increment numbers


if i have a sheet with 01 in cell A1 next time i open this sheet i would like it to be 02

does it make sence


many thanks jason

 

[lenze]

You can do this with code, but it can be tricky. Do you want the number to increase EVERYTIME the WB is opened??
Big question and you might consider a template

HTH

lenze

 

[j4ymf]

yes i would like it everytime thr wb is opened, can you give me some examples of the code or where I can find a template please?

 

[unnilennium78]

Copy this to

ThisWorkbook

Public Sub Workbook_Open()
Call test
ThisWorkbook.Save
End Sub

Copy this to new Module

Sub test()

Dim c, d, i

For c = 2 To 65536

    If Range("A" & c) = 0 Then
    d = c - 1
    i = Range("A" & d)
    GoTo 10
    End If
        
Next c

10 Range("A" & i + 1) = i + 1

End Sub

u will notice that after running the MACRO the excel workbook will save, in order for this to work the workbook has to be saved everytime before u close the excel.... hence made to save automatically before u work on it & close the excel......

Haaa haaa.... I'm new at this MACRO thingy.... so if this is not what u wanted, pls accept my apology

 

[j4ymf]

thanks unnilennium78

im getting a compile error

am i doing somthing wrong?

jay

 

[Boller]

Assuming "Sheet1" :-

Private Sub Workbook_Open()
Sheets("Sheet1").[A1] = Sheets("Sheet1").[A1] + 1
ThisWorkbook.Save
End Sub

This does not prevent users from changing the value in A1 after the WorkBook is opened.
If you want to do prevent user changes to A1 but don't know how, post again.

 

[unnilennium78]

thanks unnilennium78

im getting a compile error

am i doing somthing wrong?

jay

Did u copy the correct code the the specified location ......

The Sub workbook Open portion has to be copy to ThisWorkbook

while the

Sub Test has to be copied to a New Module.....

By the way what is ur error ??