Forum Discussion
Increment relative cell (A2) in Excel, WITHOUT saving the file
I don't see any code.
But if you want the number to increment without saving the workbook, you'll have to store the last value used elsewhere, for example in another workbook or in a text file.
Without knowing what you currently have it's impossible to provide more detailed help.
Hi HansVogelaar,
Wow so sorry! I literally just slapped myself haha. Apologies here is the coding I have so far, that is not working:
Private Sub Workbook_Open()
Dim hiddenWs As Worksheet
Set hiddenWs = ThisWorkbook.Sheets("Hidden")
Dim currentCount As Long
currentCount = CLng(hiddenWs.Range("A2").Value)
currentCount = currentCount + 1
hiddenWs.Range("A2").Value = currentCount
ThisWorkbook.Sheets("Sheet1").Range("A2").Value = Format(currentCount, "0000000")
ThisWorkbook.Sheets("Sheet1").Range("P10").Value = Format(Date, "mm/dd/yyyy")
End Sub
Thank you in advance for your help and time!That code will work if you save the workbook when you close it. If you don't save it, the value of Hidden!A2 will be the same as the previous time you opened the workbook.
As an alternative, create a text file named InvoiceNo.txt and enter the last used value of the invoice number in it, then press Enter. Save and close the file.
The code can then be:
Private Sub Workbook_Open() Dim strFile As String Dim f As Integer Dim InvoiceString As String Dim invoiceNo As Long strFile = ThisWorkbook.Path & "\InvoiceNo.txt" f = FreeFile Open strFile For Input As #f Input #f, InvoiceString Close #f invoiceNo = CLng(InvoiceString) + 1 Worksheets("Sheet1").Range("A2").Value = "'" & Format(invoiceNo, "0000000") f = FreeFile Open strFile For Output As #f Print #f, invoiceNo Close #f End Sub
