Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

Janet from England posed today's question. We need to know how to set up a macro which will assign a unique invoice number, how to set up a second macro which will save the completed invoice as the unique number.

Here is the hypothetical application that I am imagining. There is a workbook with an Invoice worksheet and a Menu worksheet. I propose storing the last invoice number on an out of the way cell on the Menu worksheet, lets say Z1. If the invoice number is supposed to go in cell H2 of the invoice worksheet, then this macro would do the trick:


Sub GetNextInvoiceNumber()
LastInv = Worksheets("Menu").Range("Z1").value
NextInv = LastInv + 1
Worksheets("Invoice").Range("H2").value = NextInv
Worksheets("Menu").Range("Z1").value = NextInv
End Sub

Then, to save the invoice, something like this:


Sub SaveInvWithNewName()
' Copy Invoice to a new workbook
Worksheets("Invoice").Copy
NewFN = Range("H2").value & ".xls"
ActiveWorkbook.SaveAs NewFN
ActiveWorkbook.Close
End Sub


By Bill Jelen on 09-Nov-2001

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.