Creating a button/macro in excel to generate new workbook and save file as a number in a sequence
Hi there
I was wondering if anyone would be able to help me with the following
I am after creating a button on a workbook front sheet that once pressed it generates a copy of a template workbook and saves it in a number sequence that increases everytime its generated. ie. when the button is pressed it generates workbook01 the next time it is pressed it will use the same template workbook as the first time but will name it and save it as workbook02 etc.
regards m.wilson
Does this work for you?
I can think of 2 ways to do this depending on how you will be saving these.
The first is if you are pressing this button multiple times in one session and save workbook01,02,03 etc. Then close out. The next time you decide you want to run the macro again it will start over at workbook01 again:
Dim SaveCounter As Integer
Sub save()
SaveCounter = SaveCounter + 1
ActiveWorkbook.SaveAs "Workbook" & SaveCounter
End Sub
The second way I can think to do this, is to pick a cell on your spreadsheet with the button. In this cell you can keep a counter of how many times the button was pressed over the life of the button:
Sub save()
Range("A1") = Range("A1") + 1
ActiveWorkbook.SaveAs "Workbook" & Range("A1").Value
End Sub
Let me know if these don't work for you and maybe we can figure something else out!
Sincerely,
-Max