How can I create code that will enable auto-update of the range location when the Excel Model is updated and the location of the target cells changes?

[I_love_VBA]

Dear programming enthusiasts and professionals,

I have created macro in Excel VBA to perform iterative operations. The intention of this code is to paste the values from one range of cells, which contain formulas, into another range.

In this code, I have specified the location of the ranges (both for the ones being copied and for the ones being pasted).

During the modification of the Excel Model, I change the number of rows or columns (by adding or deleting), thus changing the actual location of the range of cells. As an example, what used to be Range("N786:BT786") can become Range("N650:BT650").

I would certainly appreciate if you could help to modify my code so that the ranges would not be fixed to the static location, but rather automatically update as the model itself is being updated.

Below you may find my code.

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub calculations_update()

Application.DisplayStatusBar =False

Application.ScreenUpdating =False

Dim StartTime AsDouble

Dim SecondsElapsed AsDouble

StartTime = Timer

DoWhile i <95

i = i +1

ThisWorkbook.Worksheets("Electrity").Activate

Range("N786:BT786").Value = Range("N787: BT787").Value

Range("R826:BT826").Value = Range("R827: BT827").Value

ThisWorkbook.Worksheets("Efficiency").Activate

Range("H814").Value = Range("H815").Value

Range("H826").Value = Range("H827").Value

Range("H846").Value = Range("H847").Value

Loop

SecondsElapsed = Round(Timer - StartTime,2)

MsgBox "The update was successful in "&SecondsElapsed&" seconds", bInformation

EndSub</code>

 

[footoo]

Assign a Name to each range, then in your macro refer to the ranges like this : Range("rangename​")

 

[I_love_VBA]

Thanks a lot, it worked! Now when I add/remove columns/rows, the macro is not affected.