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.
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