We use A1 value as new sheet name; Sub Insert_Sheet_Names() For Each c In Selection With SheetsAdd(After=ActiveSheet) Name = cValue End With Next c End Sub Now let's talk through it The Sub and related End Sub lines define the procedure name (Insert_Sheet_Names) and the body of the code The For Each and related Next lines define a collection loop A loop is a codeTo return the sheet name in a cell, use CELL, FIND and MID in Excel There's no builtin function in Excel that can get the sheet name 1 The CELL function below returns the complete path, workbook name and current worksheet name Note instead of using A1, you can refer to any cell on the first worksheet to get the name of this worksheet
Return Sheet Name Into A Cell Excel Formula Youtube