Autopopulate sequential reference number field

[elanda]

Hello,

I am trying to do some basic VBA in my spreadheet, I just want to create an auto-open statement that inputs then next sequential number in column A1 every time the s/s is opened i.e.1,2,3,4 .....

I am using excel 2007 and my knowledge is very limited. I used a code already posted previously by Shane (which is not exactly want I want):

Sub AUTO_OPEN

Range("A1") = Format(Range("A65536"), "YYYYMMDD") & Right(Range("A1"), 3) + 1

End Sub

(the first time you open the file you would need to add the first entry

'20021122001)

But this does not work and I get a compile error when run thru debugger.

Any suggestions for this seemingly simple piece of code!!

Cheers
Adrian

 

[Smitty]

Welcome to the Board!

Providing it's numeric you can use:

Range("A1").Value = Range("A1").Value + 1

Note that I'd use the Workbook_Open event over Auto Open.

HTH,

 

[elanda]

Hi Smitty,

Thanks for the speedy reply.

I have entered as my vb in sheet1:

Sub Workbook_Open()
Range("A1").Value = Range("A1").Value + 1
End Sub

I have saved s/s as an xlsm, changed the columnA format to Number (from General) entered 1 in A1 (expecting to see 2 in the A2 column next time I open it and so on). But nothing happens. Am I missing some basic step here?
Cheers
Adrian

 

[pgc01]

Hi Adrian
Welcome to the board

The Workbook_Open() is a workbook event, not a worksheet event.

You should write it in the ThisWorkbook module, referring to the worksheet.

For ex., paste in the ThisWorkbook module:

Sub Workbook_Open()
 
With Worksheets("Sheet1").Range("A1")
    .Value = .Value + 1
End With
End Sub

 

[elanda]

Thanks for your speedy responses.
Finally I have got this to update everytime I open it. With much thanks! however it only updates cell A1 everytime.
I want it to ADD another row with next sequential no. i.e.

1 data data
2 data data
3 data data
4 data data
5 data data
6 data data
7 data data
8 data data
9...... and so on.....

Adrian