Get next number in sequence

[Gazball]

I am trying to create essentially a delivery docket template, I have it all setup and currently you need to look in the folder to see what the next number sequence is the it automatically saves with the number you have put in and then in the suppliers folder or creates a new folder (information gained from this website)

But what I would really like to do it when you open the template workbook it grabs the next number sequence maybe from another source file and then when you hit the save button it then saves the file (which is currently working) and exports the delivery docket number the supplier name and then possibly the first line of the items being sent to the file that has the number system in it so then possibly later down the track I can look up data by the delivery docket number.

Currently this is the code I am up to for the saving but I want to add automated sequential numbering system, the file saves in the supplier folder with the name of the delivery docket number and then the description from line one.

Any help would be amazing please





Option Explicit


Const MYPATH As String = "F:\Data\2584\08 General\20 Warehouse\7.Outward Deliveries\Delivery Dockets\"

Sub IfNewFolder()


Dim part3 As String

part3 = Range("D3").Value

If Len(Dir(MYPATH & part3, vbDirectory)) = 0 Then
MkDir MYPATH & part3
End If

End Sub
Sub SaveCustomizedCourse()

Dim part1 As String
Dim part3 As String
Dim part4 As String

part1 = Range("O2").Value <delivery docket number>
part3 = Range("D3").Value <supplier name>
part4 = Range("G10").Value <line one of the item description>

IfNewFolder

ActiveWorkbook.SaveAs Filename:= _
MYPATH & part3 & "\" & part1 & " - " & part4 & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

 

[keithmct]

can you amend your code to add 1 to "O2" each time?
something like Range("O2") = Range("O2") + 1

 

[Gazball]

can you amend your code to add 1 to "O2" each time?
something like Range("O2") = Range("O2") + 1

Not sure if that would work as it is a locked template as the people using it cannot be trusted to not break it.

I am trying to make it dummy proof, which is proving more difficult

 

[keithmct]

try this which I've cobbled together from something I use:

make a new workbook called "docket number summary" in the same folder as delivery dockets and list your docket numbers in column A.
Set it out thus:

I made a button "save" and used the following code but you can incorporate it into your existing save macro

Private Sub savebutton_Click()
Set d = Workbooks("docket number summary").Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
ThisWorkbook.Activate

d.Value = Sheets("Sheet1").Range("B3").Value 'Transfers date to docket number summary column B on summary, a date in the summary is handy as well
d.Offset(0, 1).Value = Sheets("Sheet1").Range("D3").Value 'Transfers Client Name to column C on summary
d.Offset(0, 2).Value = Sheets("Sheet1").Range("G10").Value 'description line 1 to D on summary
Range("D3,G10,O2").ClearContents 'and any other contents you want to add
End Sub

in VBA, ThisWorkbook module, paste the following:

Function BookOpen(wbName As String) As Boolean
    On Error Resume Next
    BookOpen = Len(Workbooks(wbName).Name)
End Function

Private Sub workbook_open()
If Not BookOpen("docket number summary.xlsm") Then Workbooks.Open Filename:= _
" F:\Data\2584\08 General\20 Warehouse\7.Outward Deliveries\docket number summary.xlsm", UpdateLinks:=0
Set c = Workbooks("docket number summary.xlsm").Sheets("sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1, -2) ' grabs the next unused docket number
ThisWorkbook.Activate
Range("O2") = c
End Sub

I tested it but make sure you test on a copy of your workbook before applying. I hope this makes sense but let me know if anything is wrong. I'm no expert by any means.

 

[keithmct]

you might also need to add code to close the summary workbook after saving