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