Worksheet Duplication Code Help

Hi Guys,

I have an excel with a macro, which duplicates an invoice template on the first sheet to new worksheets. Also, these newly generated invoices are numbered automatically in a sequence. I have a new file which requires the same procedure. But the cell, range etc are different. Could some one help in re-coding the below code to sui my needs. The invoice number in the old file was 13-01-1000 (YY-MM-1000). So when a new invoice was generated the sequence would be 13-01-1000, 13-01-1001, and so on.

The new invoicing sequence will be "TWS/0902/20161000", where TWS/0902/2016 will be constant and the sequential numbers will be form 1000, 1001, 1002 etc. TWS/0902/20161000, TWS/0902/20161001, etc will be the sequence.

The previous cell range for the invoice number was 'J6' (as mentioned in the below code). New invoice template has the invoice number in cell 'J13'

Code:

---

Sub Create_New_Invoice()
 
  Dim InvNo As String
 
  Application.ScreenUpdating = False
  EEvents = True
  With wksFormat
    With .Range("J6")
      InvNo = .Value
      InvNo = Left(InvNo, 6) & Format(Right(InvNo, 4) + 1, "0000")
      .Value = InvNo
    End With
    .Range("J7").Value = Date
    .Copy After:=Worksheets(Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = InvNo
    ActiveSheet.Buttons("cmdAdd").Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
  EEvents = False

End Sub

---

Also, with the generation of a new invoice on a new worksheet, the invoice number automatically becomes the name of the worksheet based on the below code.
Can the same be done for the new invoicing criteria in cell 'J13' as mentioned above. Wherein, the name of the worksheet changes to 1000, 1001, etc instead of the whole TWS/0902/20161000.

Code:

---

Sub NameSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Range("J6") <> "" Then
ws.Name = ws.Range("J6").Value
End If
Next ws
End Sub

---

Thanks

Hi

I hope this will do

Code:

---

Sub Create_New_Invoice()
 
    Dim InvNo As String
   
    Application.ScreenUpdating = False
    EEvents = True
 
    Const ConstInvNo    As String = "TWS/0902/2016"
 
    With wksFormat
        With .Range("J13")
            InvNo = .Value
            InvNo = ConstInvNo & Format(Right(InvNo, 4) + 1, "0000")
            .Value = InvNo
        End With
        .Range("J7").Value = Date
        .Copy After:=Worksheets(Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = Right(InvNo, 4)
        ActiveSheet.Buttons("cmdAdd").Delete
        On Error GoTo 0
    End With
    Application.ScreenUpdating = True
    EEvents = False

End Sub

---

The above code doesn't seem to work for me. I get the following error:

Run-time error '424': Object Required. Once I click debug, With

Code:

---

.Range("K13")

---

shows highlighted in yellow. (I changed the invoice no. cell to K13 from J13.

Please help.

You have to declare the variable wksFormat

Quote:

---

Originally Posted by Admin

You have to declare the variable wksFormat

---

Could you please explain this with details, as I am not familiar with codes.

Declare this variable

Code:

---

set wksFormat = Worksheets("YourSheetName")

---

just above the

Code:

---

With wksFormat

---

line.

obviously replace the "yoursheetname" with actual sheet name :)