rename a worksheet adding a sequential number at the end

[BeSmart]

Hi All

I need to create a code and I’m not sure if it’s even possible – your help
would be greatly appreciated:

Excel 2003
Relevant part of current code:

ActiveSheet.Name = "MBA" & " " & Sheets("Sheet2").Range("A2").Value

I need to:

- add a check into my macro to see if the new worksheet name "MBA Wk1"
already exists

- if TRUE, then I need to check if there are any values on
Sheets("Sheet2").Range("C131:C150"),

- if also TRUE then I need to create the new worksheet with a sequential
number added to the end of the new worksheet name e.g:

MBA Wk1 becomes MBA Wk1 (2),

otherwise call the next macro “week2()â€

The macro may be run a few times as data is revised - therefore it needs to
create update the sequential number e.g. MBA Wk1 (3) next time etc.

 

[Shasur]

Hi

Here is a method to check existence of sheetname in current workbook

Function SheetExists(ByVal ShtName As String) As Boolean

On Error GoTo Err_Sht

Dim oWS As Worksheet

Set oWS = ActiveWorkbook.Worksheets(ShtName)

If Not oWS Is Nothing Then Set oWS = Nothing

SheetExists = True

Exit Function:
Err_Sht:
Err.Clear
SheetExists = False

End Function

Cheers
Shasur