copy sheet & rename using macro

TwylaG

Board Regular
Joined
Jun 6, 2008
Messages
101
Hi all
I need a smidge of help with a macro.
I have a workbook with the first sheet in it called 1081, (the initial name is irrelevant, it could be 1056 or 1238) and I want to use a macro to make a copy of that worksheet and rename it to the next number 1082 (or 1057 or 1239 from the alternate examples). However if 1082 already exists I want it to call it 1083 or whatever the next non-used number is; always copying that first sheet (which is a blank template). I've found various snippets on the web to copy & rename, but I can't seem to get them to work (they generate various runtime errors). Does anyone know anything offhand that will work? I thought I had it there before holidays, but when I came back it was only making new blank sheets with the correct name & not copying the template into it. Here's what I've got so far.
Code:
Sub AddWs()
Dim i As Long, wsName As String, temp As String
Worksheets.Add after:=Worksheets(Worksheets.Count)
wsName = "1081"
If WorksheetExists(wsName) Then
    temp = Left(wsName, 3)
    i = 1
    wsName = temp & i
    Do While WorksheetExists(wsName)
        i = i + 1
        wsName = temp & i
    Loop
End If
ActiveSheet.Name = wsName
End Sub


Function WorksheetExists(wsName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(wsName).Name = wsName
On Error GoTo 0
End Function
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
TwylaG,

Worksheets.Add..... is adding a new blank sheet.
You need Sheets("1081").Copy.....

Code:
Sub AddWs()
Dim i As Long, wsName As String, temp As String
wsName = "1081"
Sheets(wsName).Copy after:=Worksheets(Worksheets.Count)
If WorksheetExists(wsName) Then
    temp = Left(wsName, 3)
    i = 1
    wsName = temp & i
    Do While WorksheetExists(wsName)
        i = i + 1
        wsName = temp & i
    Loop
End If
ActiveSheet.Name = wsName
End Sub

Use the Function WorksheetExists as it is.


Do you realise that the way the sheet number is being incremented in the above that after 1089 it wil prodice 10810, 10811 etc?

If you want 1089 1090 1091 etc then maybe use more like the code below.
Also in code below, I have not used the name of the template sheet. If it can be Guarenteed that your template sheet is always going to be the first sheet then you can refer to it as Sheet(1). That way, the code might be more generic if as you say your first sheet may be 1081 or 1057 1239 or whatever.

Code:
Sub AddWs2()
Dim i As Long, wsName As String, temp As String
wsName = Sheets(1).Name
Sheets(1).Copy after:=Worksheets(Worksheets.Count)
If WorksheetExists(wsName) Then
    
    wsName = wsName + 1
    Do While WorksheetExists(wsName)
        i = i + 1
        wsName = wsName + 1
    Loop
End If
ActiveSheet.Name = wsName
End Sub

Hope that helps.
 
Last edited:
Upvote 0
I haven't done much vba but am somewhat familiar with a bunch of old programming languages so I think I am following where you're going with this. No I wasn't aware that it would increment to 10810 ... thanks for the tip :) The code works very nicely, so now I will add a button so my co-worker can use it without me having to show her too much.
Thank you very much for helping!
 
Upvote 0
I swear it worked so nicely for the first half hour and now I get "sub or function not defined". I thought maybe I hit something on the keyboard so re-copied/pasted and now its a no-go.
 
Upvote 0
It will work as you know from half an hour ago.
First off, ensure that both the Add code and the Function are in the same module.
It could be a sheet module but I would suggest that you have them both in a code module.(vb Editor - Insert - Module if you don't have a module available)

Otherwis,if you have been in the vb editor. Just make sure that you have not accidentally altered any code. If necessary delete it and copy it across from the forum post again.
 
Upvote 0
Ok, I am way confused. I didn't change anything and now its working fine again. It must be Monday. Thank you again.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top