I'm using Excel 2010 and I'd like to accomplish the following using vba code:
Copy the last worksheet in workbook
Prompt user to enter a name for the newly copied worksheet and rename the worksheet with the User's input
Rename part of all existing named ranges in the newly copied worksheet. For example, using the string that the user entered in Step 2, is it possible to use the vba InStr function to replace Name1
as presented in the attached spreadsheet
Please refer to attachment for example
Any help will be greatly appreciated.