Excel: Excel not renaming the copied sheet if name already exits

I have written below code but its not working as required: My requirement is: Copy Sheet1 of Source.xlsm workbook to another workbook, s.xlsx and then rename the copied worksheet to value of D1 cell of this sheet. If sheet name of same name do not exists then it should rename and if name exits it should go to else condition, prompt an input box asking for new name and then rename accordingly. My code is giving Run-time error '1004': Can not rename a sheet to name as of another sheet. And this line Sheet.Name = range("D1") of else condition is highlighted. Here my code:

Sub savesheet()        
    Dim sPath As String
    Dim wbPath1 As Workbook
    Dim wsName As String
    sPath = Application.ActiveWorkbook.Path & "\s\s.xlsx"
            Set wbPath1 = Workbooks.Open(sPath)
            Workbooks("Cash Loading.xlsm").Sheets("Sheet1").Copy After:=wbPath1.Sheets(1)
            For Each Sheet In Workbooks("s.xlsx").Sheets
                If Not Sheet.Name = range("D1") Then
                    Sheet.Name = range("D1")
                   wsName = InputBox("Name already exits,Please enter new name")
                   Sheet.Name = wsName
                End If
End Sub

This question generated 8 answers. To proceed to the answers, click here.

This thread is current as of February 11, 2018.

For more resources for Microsoft Excel