Saving a File Generates a Sequential Number Name

[Shauna Koppang]

Again, for those who have been helping me THANKS again. I
am almost there.

The client wants that when you save the file, it looks in
the folder they are to be saved in, and generates a
sequential number as a file name, which would then display
as a PO number in the workbook in a cell, if that makes
sense.

Any ideas?

Thanks!
Shauna

 

[Diana]

Hi Shauna,

If I understand what the situation is correctly this
could be the solution. "PO_Number" is the cell in excel
that would match the file name. It starting number is up
to you to decide. I would lock it for manual editing. So
each time you click on a button to save the information
the PO_Number's value would increase by one and this
would be the files name.

Public Sub SaveAs()

Dim DestinationFile As Integer

'select the cell that reflects the PO number in the
workbook
Range("PO_Number").Select
'set the cell to the next seq number (PO Number)
ActiveCell = ActiveCell + 1
'Define the Target File Name
DestinationFile = ActiveCell
ActiveWorkbook.SaveCopyAs "\\your path\" &
DestinationFile & ".xls"

End Sub