PDA

View Full Version : Autonumbering invoice then saved to another worksheet in the same workbook



merima
07-31-2012, 02:10 PM
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
07-31-2012, 04:11 PM
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
07-31-2012, 09:23 PM
I could do that, but can you help me with code to do that automaticaly with macros?

Bob Phillips
08-01-2012, 12:32 AM
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

merima
08-01-2012, 02:32 PM
Thanks but code does not work in my Workbook
I rearranged code into

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 With

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
08-02-2012, 05:05 AM
You need to put it into a macro, and call that macro from somewhere, such as a button on a sheet.

merima
08-02-2012, 01:23 PM
Aha! :blush
Thank you so much I am really grateful for your help.

I changed code into


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

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
08-02-2012, 01:42 PM
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
08-03-2012, 07:15 AM
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
08-03-2012, 07:34 AM
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
08-03-2012, 10:34 PM
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
08-04-2012, 01:58 AM
Next time I am in Slovenia, I will take you up on it.