Auto Number increase

[MatthewHughes]

Hello all.

i have searched this forum and found things very close, But none of them work for my self.

what i need help with is im trying to get a number to auto increase each time the jobsheet is printed,

it will also be accessed by more than one computer/user so also needs no write protection (i dont know how it keeps activating?)

i would upload the file but cant seem to find where to do it.

im using office pro plus 2013 if that makes any difference?

Thank you in advance.

 

[MatthewHughes]

Hi Matt,

the below will still work on print, and not on open as requested.

You will need to create a file somewhere called logfile.xlsx and just enter the latest number you have used in cell a1 and save then close.
copy the below macro to the ThisWorkbook section of the VBA editor for the workbook you will be using, and change "c:\test\logfile.xlsx" to the path of your logfile

then save your file (or copy of to test)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim TW As Workbook
Dim lf As Workbook
Set TW = ThisWorkbook
Set lf = Workbooks.Open("c:\test\logfile.xlsx")
With TW
ActiveSheet.Range("H4").Value = lf.Sheets(1).Range("a1").Value + 1
End With
With lf
Application.ScreenUpdating = False
.Sheets(1).Cells(1, 1).Value = .Sheets(1).Cells(1, 1).Value + 1
lf.Save
lf.Close
Application.ScreenUpdating = True
End With
End Sub

each time you print, it will update the value of H4 in the logfile, so the next time it is printed you will get the next available number.

Like i said, this is not infallible, but the logfile should only be open for a couple of seconds while you print, so the likelyhood of the file being locked is small.


Regards,

Dan.

Can i say a huge thank you.

i have a couple of other little issues (**** networks) that i will sort out but this has been a great help thank you once again.

Matt