Worksheet Duplication Code Help

[msiyab]

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'


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.


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

[Admin]

Hi

I hope this will do


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

[msiyab]

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
.Range("K13") shows highlighted in yellow. (I changed the invoice no. cell to K13 from J13.

Please help.

[Admin]

You have to declare the variable wksFormat

[msiyab]

You have to declare the variable wksFormat

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

[Admin]

Declare this variable


set wksFormat = Worksheets("YourSheetName")

just above the
With wksFormat line.

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