Excel VBA - Copy andpaste formula without incrementing

Question

I have a number of formulas similar to this one:

=MID('Sheet1'!O40,1,5)

I'm trying to copy and paste this formula to a blank cell without incrementing the cell reference and updating the worksheet reference.

Desired End Result:

=MID('Sheet2'!O40,1,5)

I've used the below code as a test sample but the copied formula auto increments the cell reference. Is there anyway to copy and paste formulas without incrementing?

Range("E2").Copy
Range("E126").PasteSpecial xlPasteFormulas

So, are you sure that 'Sheet1' of the original formula must be transformed in 'Sheet2' in the target formula? You do not want cells incrementing, but you need Sheet name incrementing...? — FaneDuru
I think we need more detail - is Sheet2 the next sheet by index? — BigBen
Correct. I don't want the cell references incrementing but I need to change the Sheet name on copy and paste of the formulas. With the test sample it automatically increments the cell references. Sheet 2 is next in index correct — Mors

Mors Mors · Accepted Answer · 2020-02-19T17:48:20

I've come up with a solution below:

Range("E126").Formula = Range("E2").Formula
Set r = Range("E126").SpecialCells(xlCellTypeFormulas)
r.Replace What:="Sheet1", Replacement:="Sheet2", LookAt:=xlPart

Excel VBA - Copy andpaste formula without incrementing

1 Answers