title: How to use powershell to select and copy columns and rows in which data is present in new workbook.
body: <p>I want to select and copy columns and rows in which data is present in new workbook. </p>
  
  <p>I found this link which helped me just do the above.
  <a href="https://stackoverflow.com/questions/23703354/how-to-use-powershell-to-copy-several-excel-worksheets-and-make-a-new-one">How to use powershell to copy several excel worksheets and make a new one?</a></p>
  
  <p>But the only change i want is that in the progam given below they fetch the first 6 columns while i want to check for columns that have data and pick them up. Can anyone help me in that.</p>
  
  <pre><code> #Get a list of files to copy from
  $Files = GCI 'C:\Users\Matt\Documents\Excel Test\' | ?{$_.Extension -Match "xlsx?"} | select -ExpandProperty FullName
  
  #Launch Excel, and make it do as its told (supress confirmations)
  $Excel = New-Object -ComObject Excel.Application
  $Excel.Visible = $True
  $Excel.DisplayAlerts = $False
  
  #Open up a new workbook
  $Dest = $Excel.Workbooks.Add()
  
  #Loop through files, opening each, selecting the Used range, and only grabbing the first 6 columns of it. Then find next available row on the destination worksheet and paste the data
  ForEach($File in $Files[0..4]){
      $Source = $Excel.Workbooks.Open($File,$true,$true)
      If(($Dest.ActiveSheet.UsedRange.Count -eq 1) -and ([String]::IsNullOrEmpty($Dest.ActiveSheet.Range("A1").Value2))){ #If there is only 1 used cell and it is blank select A1
          [void]$source.ActiveSheet.Range("A1","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
          [void]$Dest.Activate()
          [void]$Dest.ActiveSheet.Range("A1").Select()
      }Else{ #If there is data go to the next empty row and select Column A
          [void]$source.ActiveSheet.Range("A2","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
          [void]$Dest.Activate()
          [void]$Dest.ActiveSheet.Range("A$(($Dest.ActiveSheet.UsedRange.Rows|Select -last 1).row+1)").Select()
      }
      [void]$Dest.ActiveSheet.Paste()
      $Source.Close()
  }
  $Dest.SaveAs("C:\Users\Matt\Documents\Excel Test\Book1.xlsx",51)
  $Dest.close()
  $Excel.Quit()
  </code></pre>
  
  <p>Thanks in advance
  Moe</p>