locked
Excel Copy Paste RRS feed

  • Question

  • Is there a way to copy three separate cells from one sheet and paste them into a column of another sheet?  I have found the range copy which has worked, but I want the data from different cells all in one copy function.  
    Monday, June 4, 2018 8:43 PM

All replies

  • Yes.  Just select each cell and copy it to the target location.

    Use Excel CopyTo: https://www.gemboxsoftware.com/spreadsheet/help/html/M_GemBox_Spreadsheet_CellRange_CopyTo.htm

    You can create a range of cells and use CopyTo.


    \_(ツ)_/

    Monday, June 4, 2018 9:17 PM
  • Seems you have to do it in seperate steps. It is not possible to copy non-neighbored cells by hand and it won't be possible with PowerShell, I guess.
    Tuesday, June 5, 2018 6:03 AM
  • I have tried this, but only the last cell copied continues to paste.  The other cells do not copy over. 
    Tuesday, June 5, 2018 12:02 PM
  • Then, please show your code. I guess, you have always the same destination cell.
    Tuesday, June 5, 2018 1:19 PM
  • $range1= "L20"
    $range3= "L34"
    $range4= "L39"
    $range2="B3:B5"
    $file1 = 
    $file2 = 
    $xl = new-object -c excel.application
    $xl.displayAlerts = $false 
    $wb1 = $xl.workbooks.open($file1, $null, $true) 
    $wb2 = $xl.workbooks.open($file2)
    $ws1 = $wb1.WorkSheets.item(1) 
    $ws1.activate()  
    $range = $ws1.Range($range1).Copy()
    $range = $ws1.Range($range3).Copy()
    $range = $ws1.Range($range4).Copy()


    $ws2 = $wb2.Worksheets.item(1) 
    $ws2.activate()
    $x=$ws2.Range($range2).Select()

    $ws2.Paste()  
    $wb2.Save() 

    $wb1.close($false) 
    $wb2.close($true) 
    $xl.quit()
    spps -n excel

    It ends up pasting only the last cell that I copied into the range on my wb2.   I am looking to get three cells from wb1 and paste it into the next range I want.  I have multiple sheet and three cells in each to copy from.  

    Tuesday, June 5, 2018 2:03 PM
  • This is not how it works. You have to copy a range and paste it. Copy the next range and paste it. And so on: Copy and Paste. Not copy, copy, copy, paste.

    Imagine you are doing it manually in Excel. What happens, if you copy a cell? It gets a dashed border and you can paste it. When you copy the next cell, the first dashed border disappears. 

    Simply do:

    $ws1.Range($range1).Copy()
    $ws2.Range($destination1).Paste()
    
    $ws1.Range($range2).Copy()
    $ws2.Range($destination2).Paste()
    You can store source and destination in an array of objects and use it in a loop to reduce lines of code.
    • Edited by hpotsirhc Wednesday, June 6, 2018 6:17 AM
    Wednesday, June 6, 2018 6:17 AM
  • When I do that I get:

    Method invocation failed because [System.__ComObject] does not contain a method named 'Paste'.
    At line:1 char:1
    + $destination2.Paste()
    + ~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (Paste:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    On both of my Paste options. 

    Do I have to make an array or can I have it as below:

    $range1= "L20"
    $range2= "L34"
    $range3= "L39"

    $file1 =""

    $file2 = ""

    $Excel = New-Object -ComObject excel.application 
    $Excel.visible = $true
    $Excel.displayAlerts = $false 
    $wb1 = $Excel.workbooks.open($file1, $null, $true) 
    $wb2 = $Excel.workbooks.open($file2)
    $ws1 = $wb1.WorkSheets.item("All") 
    $ws1.activate()  
    $ws2 = $wb2.Worksheets.item("UserNames") 
    $ws2.activate()

    $destination1=$ws2.Range("B3")
    $destination2=$ws2.Range("B4")

    $ws1.Range($range1).Copy() | Out-Null
    $destination1.Paste()

    $ws1.Range($range2).Copy() |Out-Null
    $destination2.Paste()

    $wb2.Save() 

    $wb1.close($false) 
    $wb2.close($true) 
    $xl.quit()
    spps -n excel

    Thursday, June 7, 2018 3:19 PM
  • I see, I made an error when I copied your code. I always use PasteSpecial.

    Friday, June 8, 2018 5:49 AM
  • Thank you.  Paste special did the trick.  

    I will be working on having it go through multiple sheet to pull the same range 1,2,3 info now.  I may need help at a later time with this.  

    Thank you all. 

    Friday, June 8, 2018 3:02 PM