Macros to add new worksheet and change name

Mahenderlalwani

New Member
Joined
Apr 8, 2017
Messages
12
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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)?
 
Upvote 0
Check This link
That didn't really answer my questions, but try this with a copy of your workbook.

Code:
Sub New_Invoice_Sheet()
  With Sheets(Sheets.Count)
    .Copy After:=Sheets(.Name)
    Sheets(Sheets.Count).Name = Format(.Name + 1, "000")
  End With
End Sub
 
Upvote 0
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.

Code:
Sub New_Invoice_Sheet()
  With Sheets(Sheets.Count)
    .Copy After:=Sheets(.Name)
    Sheets(Sheets.Count).Name = Format(.Name + 1, "000")
  End With
End Sub
 
Upvote 0
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
Code:
Sheets(Sheets.Count).Name = Format(DateValue(.Name) + 1, "mmm-dd")
 
Upvote 0
I was copying last sheet as template for the next one.

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

CA Mahender Lalwani
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top