Macros to add new worksheet and change name

[Mahenderlalwani]

Hey,

I am using Excel Invoice and for every new bill i copy the template and rename sheet as bill number say 001 , 002 , 003 and so on , i am wondering if i can insert a button so that i copy current sheet to new sheet in same workbook and have a consecutive series as sheet name (001 , 002 and so on)



Thanks in advance for any help

 

[Peter_SSs]

1. Are you copying a blank (or fairly blank) 'Template' sheet each time or are you just copying the 'active' sheet which may or may not be the highest numbered sheet?

2. Could you share the code that you are using so far (if there is any)?

 

[Mahenderlalwani]

1. Are you copying a blank (or fairly blank) 'Template' sheet each time or are you just copying the 'active' sheet which may or may not be the highest numbered sheet?

2. Could you share the code that you are using so far (if there is any)?

Check This link https://files.fm/u/hdpvjejg

 

[Peter_SSs]

Check This link

That didn't really answer my questions, but try this with a copy of your workbook.

Sub New_Invoice_Sheet()
  With Sheets(Sheets.Count)
    .Copy After:=Sheets(.Name)
    Sheets(Sheets.Count).Name = Format(.Name + 1, "000")
  End With
End Sub

 

[Zedrick13]

Hi Peter,

That code is amazing! What do I have to change if I want the sheet name to be a date instead and for it to automatically change depending on the sheet's name? Like for example, I have a sheet named "Jan-01", and then when I run the code it creates another sheet named "Jan-02". Also, if possible, it creates the next sheet depending on what date is next when jumping from a month to the next. Like this, "Feb-28", then "Mar-01".

That didn't really answer my questions, but try this with a copy of your workbook.

Sub New_Invoice_Sheet()
  With Sheets(Sheets.Count)
    .Copy After:=Sheets(.Name)
    Sheets(Sheets.Count).Name = Format(.Name + 1, "000")
  End With
End Sub

 

[Peter_SSs]

What do I have to change if I want the sheet name to be a date instead and for it to automatically change depending on the sheet's name? Like for example, I have a sheet named "Jan-01", and then when I run the code it creates another sheet named "Jan-02". Also, if possible, it creates the next sheet depending on what date is next when jumping from a month to the next. Like this, "Feb-28", then "Mar-01".

Just change this line

Sheets(Sheets.Count).Name = Format(DateValue(.Name) + 1, "mmm-dd")

 

[Zedrick13]

Thanks again, @Peter_SSs! You're the best!

 

[Peter_SSs]

Thanks again,

You're welcome.

 

[Mahenderlalwani]

I was copying last sheet as template for the next one.

Thank you. The code which you shared worked like magic !!!!

CA Mahender Lalwani

 

[Peter_SSs]

I was copying last sheet as template for the next one.

Thank you. The code which you shared worked like magic !!!!

CA Mahender Lalwani

Good news. Glad I guessed correctly.
Thanks for the follow-up.