Copy & Paste Range of Cells with fomula

Ian3 66 Reputation points
2021-12-28T21:40:00.603+00:00

I try to use the code below to copy and paste cells but formula in some cells changed. what is wrong? Thanks.

For example, before run, column H has

Header
=SUM($G$2:G2)
=SUM($G$2:G3)
=SUM($G$2:G4)
=SUM($G$2:G5)
............

After run, it has
Header
=SUM($G$2:G$4)
=SUM($G$2:G$4)
=SUM($G$2:G4)
=SUM($G$2:G5)
................

$ws.Range('A2:N2').Insert(-4121)
$ws.Range('A3:N3').Copy()
$ws.Paste($ws.Range('A2:N2'))

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,381 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Limitless Technology 39,371 Reputation points
    2021-12-29T16:33:59.27+00:00

    Hello @Ian3

    The formulas may change due to the relative position of the targets in the sheet. I would recommend using the static flag $ in the initial formula so it will not be changed after paste.

    Hope this helps with your query,

    --If the reply is helpful, please Upvote and Accept as answer--

    0 comments