Autonumbering invoice then saved to another worksheet in the same workbook

[merima]

Dear Experts.
In Excel 2007, I created a template worksheet for invoices.
I need the code that would do the following:
When the template worksheet is filled it should be saved in a new worksheet in the same Workbook,
in cell B6 it should be automatically generated a new number,
and the worksheet should be named with that number.
Once the invoice is saved in a new worksheet, the template worksheet should be cleaned and prepared for the next use.
I really appreciate your help because I am novice in writing VBA code

Merima

[Bob Phillips]

You should copy the template to a new worksheet and update that, then no need to clean the original. You can also save the last invoice number in a workbook defined name, and update that when a new invoice is created.

[merima]

I could do that, but can you help me with code to do that automaticaly with macros?

[Bob Phillips]

[VBA]Dim ws As Worksheet
Dim invNum As Long

With ThisWorkbook

.Worksheets("Template").Copy After:=.Worksheets(.Worksheets.Count)
Set ws = ActiveSheet
On Error Resume Next
invNum = Application.Evaluate(.Names("_invnum").RefersTo)
On Error GoTo 0
invNum = invNum + 1
ws.Name = "Invoice #" & invNum
.Names.Add Name:="_invnum", RefersTo:=invNum
End With
[/VBA]

[merima]

Thanks but code does not work in my Workbook
I rearranged code into

[VBA]Dim ws As Worksheet
Dim invNum As Long

With ThisWorkbook.Worksheets("PN").CopyAfter.Worksheets(.Worksheets.Count)
invNum = Application.Evaluate(.Names("B6").RefersTo)
On Error GoTo 0
invNum = invNum + 1
ws.Name = "PN#" & invNum
.Names.Add Name:="B6", RefersTo:=invNum
End Wit[/VBA]h

now returns an Compile error "Invalid outside procedure" and mark the line "With ThisWorkbook.Worksheets("PN").CopyAfter.Worksheets(.Worksheets.Count)
"
I tried several different variations but my knowledge of VBA is insufficient.
If you have any idea how to fix the code I will be awfully grateful.
Anyway thanks for trying to help.

[Bob Phillips]

You need to put it into a macro, and call that macro from somewhere, such as a button on a sheet.

[merima]

Aha!
Thank you so much I am really grateful for your help.

I changed code into

[VBA]
Sub PotniNalog()
Dim ws As Worksheet
Dim invNum As Long

With ThisWorkbook

.Worksheets("PN").Copy After:=.Worksheets(.Worksheets.Count)
Set ws = ActiveSheet
On Error Resume Next
invNum = Application.Evaluate(.Names("_B6").RefersTo)
On Error GoTo 0
invNum = invNum + 1
ws.Name = invNum
.Names.Add Name:="_B6", RefersTo:=invNum
Range("B6").Value = ActiveSheet.Name
End With
End Sub[/VBA]

Now it works great, but I still have one problem.
"Autonumbering" does not recognize that I deleted, for example one worksheet, and when I run the macro to create a new invoice, the invoice number is each time +1 higher.
Can you help me to rearange the code that will check what is in this workbook highest B6 number?

Meri

[Bob Phillips]

I don't think you should. Even cancelled invoices should not change the next available. What happens if you delete #5 of say #8, re-use #5?

[merima]

OK.
I thing that I was not clear enough what my problem is.
I checked how the macro works. A few times I run the macro. I did not save anything and I closed the workbook. When I reopened the invoice numbers have continued, althoughin in the workbook was only template sheet.
So is it possible to change code that would create new number only if the workbook is saved and if it is not that would ask if I want to save it.

I apologize because I was not already clear enough from the beginning, but I want you to know that I am extremely grateful for all your help.
If I was closer I would invite you for a coffee

Merima

[Bob Phillips]

Merima,

I am saving the latest invoice in a defined name, so if you do not save the workbook, that name will have the value from the last save. I can see what you want to do, but I cannot see how the value is being updated.

[merima]

Hello last time
Again, the problem was in me. I do not know what I tested yesterday, but now works exactly like I wanted.
So ... I owe you coffee
Thanks again.

Merima

[Bob Phillips]

Next time I am in Slovenia, I will take you up on it.