Forum Discussion
Increment relative cell (A2) in Excel, WITHOUT saving the file
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