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") Else wsName = InputBox("Name already exits,Please enter new name") Sheet.Name = wsName End If Next End Sub
This question generated 8 answers. To proceed to the answers, click here.
This thread is current as of February 11, 2018.