VBA basics
65. Insert sheet, rename (Add, Name)

Excel macro basics and applications, introduction to Excel VBA
last updated:10-18-2019

65. Insert sheet, rename (Add, Name)

Some of this topic was processed by machine translation. Original

This is an explanation for inserting a sheet (adding a new sheet) or changing the name with Macro VBA.
Inserting a sheet will often change the sheet name,
Let's remember this series of VBA, insert sheet → change sheet name.

Insert sheet

Use Sheets.Add and Worksheets.Add methods to outperform sheets in VBA.
Sheets.Add Before, After, Count, Type
Worksheets.Add Before, After, Count, Type

Before A new sheet is added immediately before the specified sheet.
After Adds a new sheet immediately after the specified sheet.
Count Specify the number of sheets to add. The default is 1.
Type Specify the worksheet type. The default is xlWorksheet.

Named argument.

Return value
Object type value that represents a worksheet, chart sheet, or macro sheet

If both are omitted, it will be inserted immediately before the active sheet.

If you add multiple sheets at the same time, subsequent macros will be difficult to write.
Even when adding multiple sheets, it is better to add one sheet at a time.
It is better not to use this argument.

Specify the worksheet type.

xlChart Graph
xlDialogSheet Dialog sheet
xlExcel4IntlMacroSheet Excel version 4 International Macro sheet
xlExcel4MacroSheet Excel version 4 macro sheet
xlWorksheet worksheet

The default value is xlWorksheet, which is a worksheet.
Therefore, in most cases this argument is not specified.

An example of using the Add method.
The worksheet is added immediately before the active sheet.

Sheets.Add After:=Sheets(Sheets.Count)
A worksheet is added at the end of the sheet.

Rename sheet

To change sheet / e in VBA:
Sheet object Name property
Change the value of this property.
Sheet object. Name = "name"

Sheet objects
Worksheets("Sheet name")
Object variable (Worksheet type)
And so on.

Example of using the Name property
Worksheets(1).Name = "name"

Examples of sheet insertion and name change

Dim ws As Worksheet
Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
ws.Name = "New-sheet"

With Sheets.Add(After:=Sheets(Sheets.Count))
  .Name = "New-sheet"
End With

At the end of the sheet, a worksheet called "New Sheet" is added.
Since the return value of Sheets.Add is an object,
The former uses the return value as it is in the object variable.
The latter uses the return value with With.
Which is better,
If the range of the inserted sheet is narrow, using With makes the description simpler without the need for variable declaration.
If you want to use it afterwards, it is often easier to write VBA if you put it in an object variable.

Articles with the same theme "VBA basics"

57. Application properties (Speed up VBA, stop warnings, etc.)
58. What is a collection?
59. Collection processing (For Each)
60. Error processing (On Error)
61. "On Error GoTo" and "Exit Sub"
62. "On Error Resume Next" and the Err object
63. Open a book (Open)
64. Close / Save Book (Close, Save, SaveAs)
65. Insert sheet, rename (Add, Name)
66. Copy / Move / Delete (Copy / Move / Delete)
67. General practice question 7

  • >
  • >
  • 65. Insert sheet, rename (Add, Name)

  • If you find this site useful, please share and bookmark.

    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.

    If you find this site useful, please share and bookmark.
    To the bottom of the text