Excel wont rename sheet with vba code

Question

I am trying to copy sheet1 of workbook name Source.xlsm to workbook name s.xlsx. And then rename the copied sheet to a column value say D1. My code copies the sheet properly but while renaming it is giving error.

Instead of displaying message name already exits it is going to else part and giving error: Run-time error '1004': Can not rename a sheet to name as of another sheet. And this line Sheet.Name = range("D1") is highlighted.

Plese correct me what i am doing wrong.

My Code is:

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("Source.xlsm").Sheets("Sheet1").Copy After:=wbPath1.Sheets(1)

        'For Each Sheet In ActiveWorkbook.Sheets
        For Each Sheet In Workbooks("s.xlsx").Sheets
            If Sheet.Name = range("D1") Then
             MsgBox "name already exits"
              Exit Sub

             Else
                Sheet.Name = range("D1")
            End If
          Next

End Sub

Your range("D1") is not the range("D1") on each worksheet. Provide proper parent worksheet reference as you loop through the worksheets in wbPath1. — user4039065
If the sheet name already exists what do you want the copied sheet to be called? Or do you want to default it to the auto generated name (e.g. sheet4)? — Alex P
@Alex Right now I just want, this code to display message box to inform sheet name already exits and exit sub. — D. Ace
@Jeeped: If your meant to change this line of code If Sheet.Name = range("D1") Then. I change it with this line If Sheet.Name = Workbooks("Cash Loading.xlsm").Sheets("Sheet1").range("D1") Then but still no luck. Could you please elaborate what u meant to say. — D. Ace
Try to change range("D1") to range("D1").value. It is even better to trace the error by assigning this range to a variable you have already decalerd: wsName = range("D1").value. And be sure you are referencing to the right workbook and sheet. So an extended code would be: wsName = Workbooks("Source.xlsm").sheets(1).range("D1").value — Andrey Ampilogov

D. Ace D. Ace · Accepted Answer · 2018-02-11T17:43:06

Changed the code and issues resolved. Below is the code for reference:

Sub movesheet3()
Dim name As String
Dim sPath As String
Dim wbPath1 As Workbook

name = Workbooks("Source.xlsm").Sheets("Sheet1").range("D1").value


sPath = Application.ActiveWorkbook.Path & "\s\s.xlsx"

       Set wbPath1 = Workbooks.Open(sPath)
       wbPath1.Activate
        'Workbooks("Source.xlsm").Sheets("Sheet1").Copy After:=wbPath1.Sheets(Sheets.Count)

For i = 1 To (Worksheets.Count)


    If ActiveWorkbook.Sheets(i).name = name Then

     MsgBox "Sheet name already exist. GO back to the sheet and enter valid name in D1 cell"

     Exit Sub
     End If

   Next
     Workbooks("Source.xlsm").Sheets("Sheet1").Copy After:=wbPath1.Sheets(Sheets.Count)

     Sheets(ActiveSheet.name).name = name
     ActiveWorkbook.Close True

End Sub

Thanks for the help everyone....Cheers

Excel wont rename sheet with vba code

1 Answers