Copying Part of a Formula

[Olatubosun]

In fact I have multiple problems. Am trying to copy and paste part of a formula to another worksheet. In some instance I need to change both the number AND the reference cell and then paste the new formula. I have been doing it manually but its killing. Surely there must a shortcut using macro:
='C:\Users\Olatubosun Macaulay\Elkanah Schools\Mid Term Templates\MTT YR 2\Class teacher MTT 2\[MTR YR 2.xlsx]Sheet1'!$B$10
In the example above I need to change everywhere I see “2” to “3” and “Sheet1'!$B$10” to “Sheet1'!$B$11” consecutively and then paste
My second problem is again twofold:
='C:\Users\Olatubosun Macaulay\Elkanah Schools\Mid Term Templates\MTT YR 2\Subject teacher MTT 2\[Maths MTR 2.xlsx]Sheet1'!$L$3
In this second example, I need to change everywhere I see “2” to “3” and the subject “ Maths” to say “English” or whatever subject applicable.

[rscsmith]

Hi

I don't think you need a macro for this.

Paste the cells in to the new sheet. Highlight the cells you want to change and do a search & replace. (CTRL-H).

If the cells need to be changed from B10 to B11, B11 to B12, etc right down the sheet, correct one, remove the $ sign, and copy it down.

Hope this helps.

Best regards, Rob.

[Olatubosun]

Copying Part of a Formula
Thanks Rob, it worked like magic! You are wonderful. Come to think of it! I had spent hours cracking my poor, old brain over how to run a macro! But your method did not solve my second problem.
My second problem is this: I have this formula in say B1
='C:\Users\Olatubosun Macaulay\Elkanah Schools\Mid Term Templates\MTT YR 2\Subject teacher MTT 2\[Maths MTR 2.xlsx]Sheet1'!$E$3
In B2, I have this different formula
='C:\Users\Olatubosun Macaulay\Elkanah Schools\Mid Term Templates\MTT YR 2\Subject teacher MTT 2\[Quantitative MTR 2.xlsx]Sheet1'!$E$3
In B3, I have yet another formula
='C:\Users\Olatubosun Macaulay\Elkanah Schools\Mid Term Templates\MTT YR 2\Subject teacher MTT 2\[Science MTR 2.xlsx]Sheet1'!$E$3
And so on, right down to the last subject.
No problem, I can use the search & replace to change “2” to “3”. But what do I do about the subjects that are also changing? Unlike the digit which runs through the cells, each cell is allocated a different subject. What do I do?