How to replace part of a formula with VBA

[rockfrawg]

Apologies if I have a hard time explaining what I'm doing up to the point where i'm having an issue...

I have created a UserForm that edits a workbook based on user entry. The user selects a value from a combo box and enters a value in a text box. Based on the combo box selection it opens the applicable workbook, creates a new sheet in that workbook and renames it based on the text box entry. It then navigates to an existing template > select all > copy > navigate to the new sheet and paste. It then navigates to a "Summary" sheet which summarizes all information on existing sheets via formulas. The VBA navigates to the bottom, inserts a row then copies the values from above (Selection.FillDown). The formulas from above contain the sheet name from the above row. I'm trying to find/replace that sheet name within the formulas with the text box value, but having trouble doing so. Here's what I've got and everything works like I'd like up to this point:

Please Login or Register  to view this content.

ActiveCell.Offset(-1,1) contains the text that needs to be replaced (in formula below: Old Sheet Name) and NewProjectName is the text box value that needs to go into the formula (New Sheet Name), which is essentially this:
='Old Sheet Name'!C$421+'Old Sheet Name'!G$421+'Old Sheet Name'!K$421+'Old Sheet Name'!O$421+'Old Sheet Name'!S$421+'Old Sheet Name'!W$421+'Old Sheet Name'!AA$421+...etc

Part of the problem is that it is not consistent whether or not the existing sheet name is a single word or multiples with spaces; e.g. it could be MySheet instead of Old Sheet Name

Thanks in advance

[rockfrawg]

It seems that this can be done with an If / Then based on determining if ActiveCell.Offset(-1,-1).Value contains a space, but I dont know how to do this...

Please Login or Register  to view this content.

[millz]

Assuming your ActiveCell is the correct cell that needs formula replaced, try changing that Selection.Replace line to these:

Please Login or Register  to view this content.

[rockfrawg]

Thx for the response.

No dice...same result I was getting with nothing being replaced.

I know it is selecting the right ActiveCell, which is actually a range of four cells that contain this formula.

[millz]

Since it's a range of 4 cells, then which cell is ActiveCell.Offset(-1, -1) ?

Assuming S's are your selected cells, the "sheet's name" is in N?

Please Login or Register  to view this content.

[rockfrawg]

Yes...that is correct. The ActiveCell is B2 while B2:E2 are all selected, and the value that needs replacing would be in A1.

[millz]

Try:

Please Login or Register  to view this content.

[rockfrawg]

Works like BOSS!

Thank you!