Excel lỗi cannot rename a sheet same name another sheet năm 2024
|
after plotting the graph iam able to save each device vs parameter combination in a new excel sheet. Show
like objSheet.Name = (InputBox("Enter the sheet name", "Enter Sheet Name")) the data is saving in the excel sheet successfully with sheet name "TEST1". now the problem is if iam trying to give the same name "TEST1" again then it is giving an error Error:'1004' Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by visual basic. how i should i recitfy this error and how should i rename the same file? could any body clarify my mistake.. regards varma
Code: dim theSheetName as string > Do TheSheetName= InputBox("Enter the sheet name", "Enter Sheet Name", TheSheetName) if TheSheetName="" then exit do If Dir(ThesheetName)<>"" then > msgbox "File already exists. Provide a different one" > else > objSheet.Name = TheSheetName > TheSheetName = "" > end if > Loop while TheSheetName<>"" ...at present time, using mainly Net 4.0, Vs 2010 Special thanks to Lothar "the Great" Haensler, Chris Eastwood , dr_Michael, ClearCode, Iouri and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus. -
You will need to add an error trap to your code. Best way to do this is to load the return value in to an independent string variable - Check the contents and if acceptable rename your sheet If the sheet name already exists (and you can check this by looping through the sheets in the current workbook) or the sheetname is null (or empty string) then prompt the user again. I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread. - 10-12-2012, 10:05 AMRe: Error message:Cannot rename a sheet to the same name as another sheetMike! Grateful to you as ever! Yes, it is now working. The only minor issue I have is, it also creates sheet1, sheet2 and sheet3, despite they are not on the list names on column "A"> Is it because by default , excel creates these sheets? 10-12-2012, 10:11 AM Re: Error message:Cannot rename a sheet to the same name as another sheetJacc! Thanks for your help! I am looking for a code which allows to create names of sheet based on cell value. If the sheet already exists, then skip and go to next one. Once these have been created, I can then do otherstaff. I know lots of people use a separate function to check if a worksheet exists, but I prefer a sub one. -
What i use is Last edited by arlu1201; 10-12-2012 at 10:34 AM. If I have helped, Don't forget to add to my reputation (click on the star below the post) Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) Use code tags when posting your VBA code: [code] Your code here [/code] - 10-12-2012, 10:42 AMRe: Error message:Cannot rename a sheet to the same name as another sheetThanks Arlu! When I re-run the macro, it keeps creating sheet names with 1,2,11,14 and etc. I thought the code should only create a new name if the name does not exist. For, e,g, the code does not create names with alpha as these names already existed. You can try it yourself using the attached and see what I mean. 10-12-2012, 10:55 AM Re: Error message:Cannot rename a sheet to the same name as another sheetJosie if at first you don't succeed try doing it the way your wife told you to -
Re: Error message:Cannot rename a sheet to the same name as another sheetThank very much all! Arlu, I have learned that line from you and it works, but difficult to remember the syntax and tend to make mistakes. |
