Copy and Rename a Worksheet

jl2509 · Jan 19th 2017

Hi Folks,
I have a workbook that has a hidden worksheet within.
There is a command button which I am trying to get working which copies the hidden sheet, places it at the end of all other sheets
then renames it.

I am trying to get the if exists error handling correct but I get error on "If WorksheetExists(wsName) Then"

If possible can some give me an idea why this is not working on my sheet.

For information, I have this method working perfectly in another workbook !
Version is Excel 2016

Thank you

Code

Sub AddWs()
Dim i As Long, wsName As String, temp As String
Sheets("Do Not Use").Copy After:=Sheets(Sheets.Count)
wsName = "New Tennant"




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

Display More

nikolassor · Jan 19th 2017

Re: Copy and Rename a Worksheet

You can try this:

Code

Sub AddWs()
    Dim i As Long, wsName As String, temp As String
    Sheets("Do Not Use").Copy After:=Sheets(Sheets.Count)
    wsName = "New Tennant"
     
    If ShtExists(wsName) Then
        temp = Left(wsName, 3)
        i = 1
        wsName = temp & i
        Do While ShtExists(wsName)
            i = i + 1
            wsName = temp & i
        Loop
    End If
    ActiveSheet.Name = wsName
End Sub




Function ShtExists(wsName As String)
   Dim s As Excel.Worksheet
   On Error Resume Next
   Set s = ActiveWorkbook.Sheets(wsName)
   On Error GoTo 0
   ShtExists = Not s Is Nothing
End Function

Display More

jl2509 · Jan 19th 2017

Re: Copy and Rename a Worksheet

Hi, Appreciate the response.

The code provided appears to work great except that it copies the hidden sheet as another hidden sheet
I need to copy the new sheet as a visible sheet if that is possible please?

Thank you

nikolassor · Jan 19th 2017

Re: Copy and Rename a Worksheet

Add this line After Dim line

Code

Worksheets("Do Not Use").Visible = True

And this Before End Sub

Code

Worksheets("Do Not Use").Visible = False

jl2509 · Jan 19th 2017

Re: Copy and Rename a Worksheet

Perfect many thanks

Copy and Rename a Worksheet

Participate now!

Share