Rename Tab Based on Cell Value & Increment Invoice Number

[hamhead69]

Hi, complete VBA newbie here. I have searched the forum and other internet sites but have not been abel to fin the code that will do what I'm looking for. I have found bits of it but not knowing VBA I can't customize it to work.

What I'm trying to do is from a template worksheet that I have in a workbook with other worksheets, I'm trying to come up with code that will create a new worksheet based on the template (copy), increment the invoice number, and rename the worksheet tab to be "Invoice # xxxx" (new invoice number from prev. step.).

I have figured out how to use a button on the template to execute, but as I said my VBA skills are lacking to say the least.

Any help is greatly appreciated!

 

[GaryWMn]

Where is the current invoice number to be found?

 

[hamhead69]

Hi Gary, it is located in cell F5

 

[GaryWMn]

Try this on a copy of your workbook and let us know if it does what you need.

Sub NewInvoice()
' NewInvoice Macro

' This macro assumes that there is a worksheet named "Template"
' and that worksheet contains the current invoice # in cell F5.

    Dim iNextInvoice As Long
    
    With Sheets("Template").Range("F5")
        iNextInvoice = .Value + 1
        .Value = iNextInvoice
    End With
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Invoice #" & Format(iNextInvoice, "0000")
End Sub

 

[hamhead69]

Works perfectly.

Thank you very much!

 

[GaryWMn]

Happy to help!

 

[hamhead69]

Gary, one more question if you don't mind. At the end of this workbook I have a sheet named Invoice Tracker which lets me see what is open/unpaid, amounts, etc. What variable would I use from the script to add from the new invoice cell F5 to the next available row in column b?

Basicall the setup is

Invoice Tracker worksheet
B C D
Invoice # Date Customer
='Invoice # 1204'!F$5 ='Invoice # 1204'!F$6 ='Invoice # 1204'!B$11

 

[hamhead69]

ok, I've figured out how to get to the Invoice Tracker worksheet. I found some code to find the next available cell in column B but it doesn't actually go to the next available row. In this case B7 is the next availablebut it goes to B17. Below is what I have so far.

Sub NewInvoice()
' NewInvoice Macro
' This macro assumes that there is a worksheet named "Template"
' and that worksheet contains the current invoice # in cell F5.
Dim iNextInvoice

With Sheets("Template").Range("F5")
iNextInvoice = .Value + 1
.Value = iNextInvoice
End With
Sheets("Template").Copy Before:=Worksheets("Template")
ActiveSheet.Name = "Invoice #" & Format(iNextInvoice, "0000")
ActiveSheet.Tab.ColorIndex = 6
LastInvoice = ActiveSheet.Name
Sheets("Invoice Tracker").Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Range("B3").End(xlDown).Offset(1, 0).Select
Cells(LastRow, 2).Select
End Sub

 

[GaryWMn]

You have two lines in your code that "select" the last row. The first one uses the End(xlDown) method. This should do the trick. The second one uses the LastRow variable as defined by the number of rows in the UsedRange. UsedRange can sometimes be unreliable depending on what has happened in the sheet while it has been open. If you stick with the End(xlDown) method you should be okay.

 

[lawbag]

Hi, wanted to comment on #4 code if I may.

(a) When the TAB is copied it also copies the MACRO, meaning every copied TAB also contains a new version of the MACRO. Is it possible to revise the MACRO so it copies the contents of the TAB but not the MACRO data?

(b) It is possible to put the new TAB rather than at the extreme right of the TAB bar, but instead on the immediate right TAB to the Template TAB.

Very useful with or without these modifications.