|
|
|
| Title | Use VBA code to add a numbered worksheet to an Excel workbook |
|---|
| Description | This example shows how to use VBA code to add a numbered worksheet to an Excel workbook. |
|---|
| Keywords | VBA, Microsoft Office, Excel |
|---|
| Categories | Office |
|---|
|
|
|
The AddNumberedSheet subroutine adds new worksheets with names MyData1, MyData2, etc. It loops through the workbook's Sheets collection looking for worksheets with names that start with MyData. When it finds one, it converts the rest of the name into a number. It keeps track of the largest number it finds.
After it has checked all of the existing worksheets, it creates a new worksheet at the end of the Sheets collection, sets its name to MyData followed by the next number, and selects that worksheet.
|
|
' Add new worksheets named MyData1, MyData2, etc.
Sub AddNumberedSheet()
Const BASE_NAME As String = "MyData"
Dim sheet_name As String
Dim i As Integer
Dim num_text As String
Dim new_num As Integer
Dim max_num As Integer
Dim new_sheet As Worksheet
' Find the largest number in a sheet name after the
' base name.
max_num = 0
For i = 1 To Sheets.Count
sheet_name = Sheets(i).Name
If Left$(sheet_name, Len(BASE_NAME)) = BASE_NAME _
Then
num_text = Mid$(sheet_name, Len(BASE_NAME) + 1)
new_num = Val(num_text)
If new_num > max_num Then max_num = new_num
End If
Next i
' Make a new sheet with a new number.
Set new_sheet = Sheets.Add(after:=Sheets(Sheets.Count))
new_sheet.Name = BASE_NAME & Format$(max_num + 1)
new_sheet.Select
End Sub
|
| |
|
| |
|
|
