VBA to replace new name of worksheets in formulas in newly copied worksheets

On the attached worksheet on "Home Page" I enter the client name on cell B4 and click the command button below it. This copies worksheets "Yearly Lease Order", "Yearly Quote Form" and "Yearly Invoice Form" and adds the contents of worksheet "Home Page" cell B4 to the copied worksheets names. ie if "Test" is in B4 then the new worksheets are called "Test Yearly Lease Order", "Test Yearly Quote Form" and "Test Yearly Invoice Form". The problem is the formulas in the new worksheets "Test Yearly Quote Form" and "Test Yearly Invoice Form" are still referencing "Yearly Lease Order". I need them to look at the newly created "Test Yearly Lease Order". Somehow I need to run a replace on "Test Yearly Quote Form" and "Test Yearly Invoice Form" to replace "Yearly Lease Order" with the contents of "Home Page" cell B4 & "Yearly Lease Order" I would like this happen with the same initial click of the command button.
Any Ideas anyone?????

Is there any one that can help me with this thread. I posted before Christmas and still havent found a solution. Spreadsheet is attached and explination on top of thread

rikki1965,
We can help, but first you need to follow forum's rule.
Please wrap you code with code tags.
You need to edit post #3 and use code tags.

Hello

I do apologise. I'm new to the forum and learning. Code Tags have been added to the code in post 3. Any help with this will be much appreciated.

Okay!
Could you please explain what are you trying to do. It is not clear from post 1.
I know your code has WorkSheetExists, so you are testing for the existence of a tab.

Sure
On the Home Page tab I enter a customer's name in cell B4. When I hit the command button below that the code in post 3 copies worksheets "Yearly Lease Order", "Yearly Quote Form" and "Yearly Invoice Form" and pastes them in three new tabs and ads the customer name in Home Page B4 to the tab name. This works great. There are formulas in "Yearly Quote Form" and "Yearly Invoice Form" that look at cells in "Yearly Lease Order". When the new sheets are created with their new names in the tabs they are still looking at the original "Yearly Lease Order" instead of the newly created "Yearly Lease Order" with the customers name added. So in other words the original 3 worksheets are a template of sorts and the formulas work within that group. When a new set of three is created from the template they need to work as a new group with the formulas only looking at that group. Can this be done with a search and replace text or is there a more efficiant way. Make any sense?

Well,
When I run the code, I get an error on the function code.
If you are copying the 3 sheets and want to copy the formulas in to new sheets, you need to copy the formulas too.
I think your code is probably coping values only.

I tried downloading the worksheet from post 1 and it worked fine for me. Ive tried adding this extra code to module 1 and it starts to work but then I get Update Values Prompt.
If I keep hitting the Esc button it goes through line by line and replaces the text as I want it to do. How can I get it to stop the Update value prompt and just running the code?

Please Login or Register  to view this content.

See attached pdf for Update Value Prompt

I do not know if this happens when a formula is linked to an outside source, but if it is a code, you can suppress the alerts and break the link.

yes that works
Ive supressed the alerts

Thanks for you help