Generate next sequence number in excel

[shahdelsol]

I am looking for macro that create next sequence number. Let's say I have 100 in A1. When I open the workbook , then I want B1 = 1 + whatever is in A1 in this case the result will be 101. Now I would want A1 becomes 101 as well. I close the file and reopen and now B1 is 102. I hope this make sense. Anyone can help? Thanks

 

[Michael M]

Put this in the ThisWorkbook module

Sub Workbook_open()
[b1] = [a1] + 1
[a1] = [b1]
End Sub

 

[shahdelsol]

Put this in the ThisWorkbook module

Sub Workbook_open()
[b1] = [a1] + 1
[a1] = [b1]
End Sub

This works but the problem is now you have to save workbook in order to get next sequence number and I don't want to save workbook. Is there a way around? Basically when I open workbook I want B1 with next number which is 1 + previous number. 100 doesn't have to be in A1 as long as there is way to keep track of the number each time I open workbook. Does this make sense?

 

[Michael M]

workbook changes won't be maintained If you don't save the workbook !

 

[shahdelsol]

workbook changes won't be maintained If you don't save the workbook !

workbook changes won't be maintained If you don't save the workbook !

Understood. My workbook will be read-only file that's why I wouldn't be able to save it unless we would create another code to take it out of read-only file , save changes and then make it read-only again. Is this do-able ?

 

[Michael M]

But read only can be turned on / off by the user anyway !
AND you will still have to save the workbook

 

[shahdelsol]

But read only can be turned on / off by the user anyway !
AND you will still have to save the workbook

Understood but it doesn't work for me. I have found something that uses notepad for sequence number which does the job but I thought I could something without using notepad. Thanks for help