Send email from VBA code by using MAPI

[nandagopal]

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.

Code Select Expand

' 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