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]

Re: Location of code. I would recommend placing your code in its own module. In VB Editor, insert a new module for your workbook. Cut the code from the Sheet it is on and paste it into the new module.

Re: Location of new sheets. Change Before: to After: in that line.

 

[hamhead69]

I'm trying to set this spreadsheet up for 2013. I copied the 2012 file and renamed it to 2013. I cleared out the 2012 data and want to start fresh with invoice 1301. I set the invoice number to 1300 in the Template workbook. When I click on the new invoice button from the Invoice Tracker sheet it creates invoice 1301, changes the tab color, activates Invoice Tracker then I get a VBA error 400. From stepping into the macro it appears to be something within this line - ActiveSheet.Range("B3").End(xlDown).Offset(1, 0).Select

Here is the whole macro;


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
Dim LastInvoice
Dim LastRow

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
ActiveSheet.Range("B3").End(xlDown).Offset(1, 0).Select
LastRow = ActiveCell.Row
ActiveSheet.Cells(LastRow, "B").Formula = "='" & LastInvoice & "'!F$5"
ActiveSheet.Cells(LastRow, "C").Formula = "='" & LastInvoice & "'!F$6"
ActiveSheet.Cells(LastRow, "D").Formula = "='" & LastInvoice & "'!B$11"
ActiveSheet.Cells(LastRow, "E").Formula = "='" & LastInvoice & "'!G$43"
End Sub

thank you in advance for your assistance.

 

[GaryWMn]

If your tracking table contains only the headers, then the End(xlDown) method will take you out of the table. You should check for the condition of the table having only one row. For example, if your table starts in B1, then you could check the number of rows in the current region based on B1. If the number of rows is 1, then just select B2. If the number of rows is greater than 1, then you can use the End(xlDown) method.

So...

If ActiveSheet.Range("B1").CurrentRegion.Rows.Count = 1 Then
  ActiveSheet.Range("B2").Select
Else
  [COLOR=#333333]ActiveSheet.Range("B1").End(xlDown).Offset(1, 0).Select
End If

Not sure where "B3" got introduced in the original, but just set the cell references based on the description above.[/COLOR]

 

[hamhead69]

Thank you, it worked perfectly. Why would it have work in the 2012 file but not the 2013 file?

 

[GaryWMn]

Really hard to say without seeing the actual workbook.