vba add worksheet and rename worksheet in Excel

Q169. In Excel, how can I add a WorkSheet and name it as required using VBA?

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub AddAndNameWorkSheet()
  3. ThisWorkBook.Sheets.Add
  4. ActiveSheet.Name = "New Sheet"
  5. End Sub

Description:

a) Line 3 - Adds a WorkSheet. If position of the sheet is to be specified After or Before sheet name could be used (ThisWorkBook.Sheets.Add After:=Sheet1 or ThisWorkBook.Sheets.Add Before:=Sheet3)

b) Line 4 - Since by default after addition of sheet, the newly created sheet is activated, ActiveSheet object could be used to rename the newly created WorkSheet.

 

You can find similar Excel Questions and Answer hereunder

1) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

2) How can I save a WorkSheet as a new WorkBook using VBA?

3) How can I insert a watermark in my Worksheet?

4) How can I convert Column numbers into Column names for use in range definition?

5) String split in vba in Excel

6) How can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

7) How can I add a Footer to my printout with automatic page numbering?

8) How can I identify the cells that are dependent on a particular cell?

9) How do I assign a macro to a shape using VBA?

10) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

11) Vba clear the contents of an entire sheet in Excel

12) How can I turn off Alerts using VBA?

13) How can I add a legend to a chart using VBA?

14) How do I enter a formula in a cell using VBA (using Absolute Reference)?

15) How to read a value from a cell in vba in Excel