Powershell, Excel, Copy specific range to another spreadsheet

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Tim Pringle Tim Pringle 2 years, 3 months ago.

  • Author
    Posts
  • #18312
    Profile photo of Jeff Wilson
    Jeff Wilson
    Participant

    Hello All, — thanks in advance , been banging my head against the wall on this one........
    I am writing powershell 4.0 code for excel spreadsheet and wanting to copy a specific range of cells IE: AB3 to AB106 using only the visible cells after applying a filter ... and copying that whole column of info. BTW only taking the visible code and skipping the first 2 lines of data, therefore specifying a set of data instead of using .entirecolumn...... Can't seem to get the code to work...
    I am looking thru the columns in an array, not that that should matter, but just an FYI. the code provided is only that of the loop i am concerned with, the other works great.... you will see the remmed out lines work.... but this is slow as it goes thru each cell indivifually. I want to take a whole block of code at once. and i can't use .entirecolumn , due to taking unwanted header data and merged cells on the target worksheet.

    [
    $CA="1"
    $ColArray = @("AB","AC","C","D","e","m","z","AA")
    Foreach ($ItemC in $colArray)
    { $ItemC1=$Itemc+$Ca
    $ItemC2=$Itemc+$Lastrow
    # copy cell from Sheet SPM Daily Report to Report Total
    #$column2=$WorkSheet.Range($ItemC1).Column
    $range=$WorkSheet.Range("$ItemC1","$ItemC2")
    #$range=$WorkSheet.Cells.Item($rowc,$column2)
    $range.Copy()
    $WorkSheet2.activate()
    $WorkSheet2.Cells.Item($row,$column)
    $range2=$WorkSheet2.Cells.Item($row,$column)
    $WorkSheet2.paste($range2)
    $column++
    }
    ]

  • #18370
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    Hey Jeff,

    I'm not certain if im picking this up right but if it's a case of just copying the results of a filter in one column except the first two rows and the end of the filter, then you could copy the entire column(s) into a variable and then remove the first and second rows and then do a check through until you find blank cells (i.e. last result has been reacher), and remove that as well. Then paste the result (past special if you wish to remove some of the formatting)

    Any chance you could send an example of how it's to look?

    cheers,

    Tim

  • #18371
    Profile photo of Jeff Wilson
    Jeff Wilson
    Participant

    I can't show you a pic, but lets say that you filter a column that has many items... i want to exclude two of the many items listed, that way if in future spreadsheets, more get added they won't be excluded make sense?
    So in other works, you have one, two, three, four, five, six, seven, eight and when you filter it, you want to "exlude" six and eight.. so you don't have to list all the rest.

    Thanks,
    jeff W.

  • #18404
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    Think i kinda get you. If there if the exclusion criteria is only two different values, then using AutoFilter would be your best bet.

    Roughly speaking, you'd need to use the select method to select the cells, then apply the autofilter method to the selection. This returns a range object, and you can use it's Autofilter method with your criteria

    http://msdn.microsoft.com/en-us/library/office/ff193884%28v=office.15%29.aspx

    The range object that is returned from that can then be copied into the clipboard and you can do whatever you like with it after that.

You must be logged in to reply to this topic.