VBA cod for Copy sheet and rename automatically followed by previous sheet invoice number.

iqubal82 · Mar 16th 2021

Dear Experts,

I have Invoice template sheet, my sheet name is EST01, EST02, EST03 and myself I doing move or copy sheet such Invoice number continuation with "EST" letters manually. So my requirement is 1. copy sheet and change sheet name such Invoice number continuation automatically. 2. That sheet name Invoice number will be update to target cell range is "J6". 3. The EST with "zero" require up to EST09, after that the Invoice number I want like EST10, EST11, EST12 continuation. Please help with suitable VBA code.

Very much thanks and regards,

**royUK** · Mar 16th 2021

Assuming you have one sheet named Template

Code

Sub AddNew()
Sheets("Template").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "EST" & Format(ThisWorkbook.Sheets.Count - 1, "000")
ActiveSheet.Range("J6").Value = ActiveSheet.Name
End Sub

It's not a very good way of creating an invoicing workbook though

iqubal82 · Mar 16th 2021

Dear Experts,

I got some error message while run the code.

The sheet name and target cell range "J6" the both could be same while run the code.

Please see the attachment and resolve my problem.

Very much thanks and regards,JAN_SALES_EST_2021.xlsx

**royUK** · Mar 16th 2021

You haven't got a template. What sheet are you copying?

iqubal82 · Mar 16th 2021

Dear Experts,

The all sheets format is same so I want copy the lost sheet name EST12 the next Invoice continuation of EST13 could be copied and same time the target cell range "J6" update with same invoice number EST13. Once again consider the "ZERO" could be come up EST09. Please resolve my problem.

Very much thanks and regards,

**royUK** · Mar 16th 2021

Try this

Code

Sub AddNew()
Worksheets(Worksheets.Count).Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "EST" & Format(ThisWorkbook.Sheets.Count, "00")
ActiveSheet.Range("J6").Value = ActiveSheet.Name
End Sub

iqubal82 · Mar 16th 2021

Dear Experts,

I got exactly Wow, super, excellent, lot of thanks.

My humble request to can I ask onemore requirement via in this thread. Please advice.

Very much thanks and regards,

**royUK** · Mar 16th 2021

If the question is related then ask it.

iqubal82 · Mar 16th 2021

Dear Expert,

Yes yes I will ask.

iqubal82 · Aug 7th 2021

Code

Sub AddNew()
Worksheets(Worksheets.Count).Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "EST" & Format(ThisWorkbook.Sheets.Count, "00")
ActiveSheet.Range("J6").Value = ActiveSheet.Name
End Sub

Dear Experts,

The above code is helping to create new copy followed by last sheet in this workbook instead of current sheet name EST number continuation

I want two codes for two types of sales invoices.

One is EST name with number continuation another one is only number continuation. Please help.

Thanks & Regards.

**royUK** · Aug 7th 2021

Attach an example of your current workbook.

iqubal82 · Aug 10th 2021

Please refer attached workbook here.

iqubal82 · Aug 11th 2021

Dear royUK,

Please refer my attached files.

Thanks & Regards,

**royUK** · Aug 11th 2021

I'll look at it as soon as I get a chance.

iqubal82 · Aug 11th 2021

Dear royUK,

Okey please.

Thanks & Regards,

iqubal82 · Aug 12th 2021

Dear royUK,

Can you see my attached file please.

**royUK** · Aug 12th 2021

When you first posted this workbook I told you that it was poorly designed and would create problems. VBA cannot replace a poorly designed workbook because as you are finding out new problems keep occurring. This is the simplest way

Code

Sub AddNew()
    Worksheets(Worksheets.Count).Copy After:=Worksheets(Worksheets.Count)
    If vbYes = MsgBox("Do you require the prefix EST?", vbYesNo + vbQuestion, "Saving Invoice") Then
        ActiveSheet.Name = "EST" & Format(ThisWorkbook.Sheets.Count, "00")
        Else: ActiveSheet.Name = Format(ThisWorkbook.Sheets.Count, "00")
    End If
    ActiveSheet.Range("J6").Value = ActiveSheet.Name
End Sub

iqubal82 · Aug 12th 2021

Dear royUK,

If I click the "Copy Sheet New' button in current work sheet the new sheet adding after LAST sheet without format.

Actually my requirement is the new sheet could be adding followed by CURRENT work sheet with previous sheet format while I click the button in current sheet.

The new sheet could be adding after CURRENT work sheet, So rearrange code please.

Thanks & Regards,

**royUK** · Aug 12th 2021

Again, that makes even less sense. Creating new invoices would be better saved to the end as it is currently doing.

By the way, I don't work for you.

iqubal82 · Aug 12th 2021

Dear royUK,

Okey I understood, the code is working but the new sheet adding followed by last work sheet but I want new sheets adding followed by current sheet thats all.

You said you don't work for me its ok but I don't know whats happened, any way thanks for your valuable help.

I say one more again that Invoice format we have using for Gold and Silver ornaments sales that reason I am asking you, So please don't hurt

Thanks & Regards,

VBA cod for Copy sheet and rename automatically followed by previous sheet invoice number.

Participate now!

Share