Copy paste entire row with insert shift down in excel

This topic contains 1 reply, has 1 voice, and was last updated by  Korhan Tezel 2 years, 1 month ago.

  • Author
    Posts
  • #29705

    Korhan Tezel
    Participant

    Hello folks,

    I am trying to write a PS script where it will select entire row or a range and then insert the selection to row below.
    $selection = $sourceWorkbook.Worksheets.item(1).Range("A125:E125").EntireRow.Select
    $selection.Copy()
    $selection.insert($xlShiftDown)

    This code is giving me the error below:
    Method invocation failed because [System.Management.Automation.PSMethod] doesn't contain a method named 'insert'

  • #29709

    Korhan Tezel
    Participant

    Ok. If anyone interested, below code works when I run it with PowerShell ISE (x86); however, when I run it via Run Command it won't work. This is the part that doesn't work:
    # xlDirection is the parent containing constants related to Direction
    $xlDirection = "microsoft.office.interop.excel.xlDirection" -as [type]
    $xlDown = $xlDirection::xlDown
    .......
    ....(There are couple of statements in between)
    .
    .
    $selection = $sourceWorkbook.Worksheets.item(1).Range("A125:E125")
    $selection.Copy()
    $selection.insert($xlDown)

    The error reads as below:
    Exception calling "Insert" with "1" argument(s): "Insert method of Range class failed"

    I don't get this error with ISE. It runs smoothly and everything works perfect.

You must be logged in to reply to this topic.