How to Macro for saving copy as csv with incremental file number

How to create a Macro for saving copy as csv with incremental file number

Requirement:

The user is managing an inventory using a barcode scanner, and the way this is done is to scan the code on each cell of the "Entradas" sheet and adding 1 to the count of the product that matches that code.

Should the person make a mistake, he can substract the product by scanning again but on the "Salidas" sheet, the sum goes to "CSV" sheet and then they upload the CSV file to the web and that pretty much does the trick.

The user wants to try to make it as automatic as posible, therefore avoiding human mistakes, so wants to make the macro that saves the "CSV" sheet as csv with an incremental file number, like file001 and then file002 and so on and that then deletes the previous data entries so they can keep using the same file without having to do any additional work.

Here is a sample workbook for a better understanding of what needs to be done:
https://docs.google.com/spreadsheets/d/1YkXJ9p_4_1dL4EgY5Yue-oz7sJo2_Q7JSQoTFG6cKA4/edit#gid=650622337

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149785-macro-for-saving-copy-as-csv-with-incremental-file-number

Solution:

Try runnng the Save_CSV_Sheet macro in the attached workbook, which is your workbook changed to a macro-enabled workbook (.xlsm file).

Sample workbook Macro.xlsm

The file001.csv, file002.csv, etc. files are created in the same folder as the workbook.

Code:

Public Sub Save_CSV_Sheet()
    
    Dim csvFileName As String
    
    csvFileName = GetNextFileName(ThisWorkbook.Path & "\file.csv")
     
    ThisWorkbook.Worksheets("CSV").Copy
    ActiveWorkbook.SaveAs csvFileName, FileFormat:=xlCSV
    ActiveWorkbook.Close False
    MsgBox "Saved CSV sheet as " & csvFileName
    
End Sub


Private Function GetNextFileName(filePath As String) As String
     
    Dim n As Integer
     
    n = 0
    Do
        n = n + 1
        GetNextFileName = Replace(filePath, "", Format(n, "000"))
    Loop Until Dir(GetNextFileName) = vbNullString
     
End Function

Obtained from the OzGrid Help Forum.

Solution provided by John_w.