excelvbacopyfilenamesworksheet

Unable to copy sheet with VBA Excel under specified name


I would like to copy the Excel sheet under the specified name.

My code looks as follows:

Sub GetJoins()

Dim tws As Worksheet, jws As Worksheet, nws As Worksheet
Dim shnm As String

Set tws = ThisWorkbook.Sheets("Frontsheet")
Set jws = ThisWorkbook.Sheets("Joint Name")

shnm = tws.Range("B4").Value
Sheets("Joint Name").Copy After:=Sheets("Frontsheet").Name = shnm
nws = jws.Copy(After:=Sheets("Frontsheet"))

End Sub

At the following line:

  nws = jws.Copy(After:=Sheets("Frontsheet"))

I get the following error: Expected function or variable

How could I copy the worksheet under the specified name?


Solution

  • Copy Worksheet After Worksheet

    Basic

    Sub GetJoins()
      
        Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
        Dim fws As Worksheet: Set fws = wb.Sheets("Frontsheet")
        Dim NewName As String: NewName = CStr(fws.Range("B4").Value)
        
        Dim jws As Worksheet: Set jws = wb.Sheets("Joint Name")
        
        jws.Copy After:=fws
        
        fws.Next.Name = NewName
        ' or:
        'Dim nws As Worksheet: Set nws = fws.Next
        'nws.Name = NewName
        
    End Sub
    

    Safer

    Sub GetJoinsSafer()
      
        Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
        Dim fws As Worksheet: Set fws = wb.Sheets("Frontsheet")
        Dim NewName As String: NewName = CStr(fws.Range("B4").Value)
        
        Dim nsh As Object:
        On Error Resume Next
            Set nsh = wb.Sheets(NewName)
        On Error GoTo 0
        
        If Not nsh Is Nothing Then
            MsgBox "A sheet named """ & nsh.Name & """ already exists!", _
                vbExclamation
            Exit Sub
        End If
        
        Dim jws As Worksheet: Set jws = wb.Sheets("Joint Name")
        
        jws.Copy After:=fws
        fws.Next.Name = NewName
        
    End Sub