Varible worksheet names in formulas

[rikk1965]

I am building a workbook used for Work orders, Quotes and Invoicing. I am using VBA to copy 3 worksheets called Order, Quote and Invoice and rename them with the customer name which is entered in a specific cell. So if I enter RRR in the cell and run the macro I get 3 new worksheets called RRR Order, RRR Quote and RRR Invoice. This is all done within the same workbook.
Quote and Invoice are populated by what is entered in Order. My problem is when the new worksheets are created with the new names the formulas in RRR Quote and RRR Invoice are still referenced to Order. I need them to be referenced to RRR Order. Is there any way to do this?

I would be grateful any help at all on this

[TMS]

Maybe do a global replace of Order to RRR Order?


Regards, TMS

[Kaper]

check this in a sheet where formulas are to be adjusted:

Please Login or Register  to view this content.

If it works as expected instead of constant strings like

Please Login or Register  to view this content.

- construct your own like:

Please Login or Register  to view this content.

[rikk1965]

Hi Kaper
I tried the first code and ran it and it made no changes at all. Any thoughts?

[Kaper]

First idea: attachment
(because on the file I prepared myself works flawlesly)

Of course no delicate data (may be alnost no data and just few formulas).

[rikk1965]

Hi Kaper
Please see attached sheet
On home page if you click new yearly lease order button it will copy the three sheets Yearly Lease Order, Yearly Quote and Yearly Invoice and rename them with the addition of the contents of Home Page (B4)
So what I need is for the formulas in the newly created Test Yearly Quote Form in cells B15:F41 to reference worksheet Test Yearly Lease Order instead of Yearly Lease Order
The same for Test Yearly Invoice Form cells B18:L28 to reference Test Yearly Invoice form instead of Yearly Invoice form.

So I will generate new 3 sheets for each customer with a new name.

Does that make any sense?:

[TMS]

Kaper's modified code for your example:

Please Login or Register  to view this content.

Regards, TMS

[Kaper]

As you probably want to replace all references (i.e. in test YQF from YQF to test YQF, and from YLO to test YLO ... and in next sheet again, the way to incorporate my proposition into your macro could be:

Please Login or Register  to view this content.

as you see the cruicial code is again basically the same:

Please Login or Register  to view this content.

[rikk1965]

Hello
When I run the above code from Kaper I now get an "Update Values" prompt. Can anyone help?

[rikk1965]

Hello
When I run the above code from Kaper I now get an "Update Values" prompt. Can anyone help?