PDA

View Full Version : Solved: How to rename a worksheet if name already exists



bananatang
08-22-2009, 06:49 PM
Hi,

Need some help here, please.....

I have set up a macro that will copy data from a userform to a new worksheet and the macro will name the worksheet as per a pre determined text box on the userform. The pre determined text box will contain a students name.

i need to amend the code in the event a worksheet with the name already exists. i would like to macro to add a number to the name. i.e. if a pupil worksheet already exists for Joe Bloggs. I would like the macro to rename the worksheet Joe Bloggs1 or if Joe bloggs1 already exist to rename as Joe Bloggs2

And if possible to have a msgbox to let the user know prior to executing the macro a students already exists with this name.

The current code i have is as follows

Worksheets("Attendance Template").Copy Before:=Worksheets(Worksheets.Count)
Set sh = ActiveSheet
sh.Name = Txtq1
Thanks

lucas
08-22-2009, 07:08 PM
Maybe try something like this to increment the number:

Dim TotalSheets As Variant
TotalSheets = Worksheets.Count - 1
Worksheets("Attendance Template").Copy Before:=Worksheets(Worksheets.Count)
Set sh = ActiveSheet
sh.Name = Txtq1 & TotalSheets + 1

mdmackillop
08-22-2009, 07:12 PM
Option Explicit
Option Compare Text
Sub addsheet()
Dim i As Long, sh As Worksheet, shname As String
Worksheets("Attendance Template").Copy Before:=Worksheets(Worksheets.Count)
shname = InputBox("New name")
For Each sh In Sheets
If sh.Name = shname Or sh.Name Like shname & "*" Then i = i + 1
Next
If i = 0 Then
ActiveSheet.Name = shname
Else
ActiveSheet.Name = shname & "-" & i
End If
End Sub

Bob Phillips
08-23-2009, 02:28 AM
Dim sh As Worksheet
Dim shName As String
Dim inc As Long

On Error Resume Next
shName = TextBox1.Text
Set sh = Worksheets(shName)
If Not sh Is Nothing Then

MsgBox "worksheet " & shName & " already exists, now incrementing"
inc = 0
Do While Not sh Is Nothing

Set sh = Nothing
inc = inc + 1
shName = shName & inc
Set sh = Worksheets(shName)
Loop
End If

ActiveSheet.Name = shName
On Error GoTo 0

bananatang
08-26-2009, 05:37 PM
Once again. thanks to all who have help me out.

BT