0

I have 2 Workbooks, I am trying to copy a range B2:B5000 from Report1 and paste it into Column A of Report2. It does copy and paste however the past goes into Report2 Column H20.. How can I specify what range to paste into? Here is what I have so far

$excel = New-Object -ComObject excel.application
$excel.Visible = $true
$SrcWkBk =$Excel.Workbooks.open("C:\Reports\Report1.xls")
$TRgWkBk =$Excel.Workbooks.open("C:\Reports\Report2.xlsx")
$SrcWkBk.WorkSheets.item(1).activate()
$SrcRng=$SrcWkBk.WorkSheets.item(1).range("B2","B5000");
$SrcRng.copy() | out-null;
$TRgWkBk.worksheets.item(1).paste();
irishombian
  • 102
  • 4
  • 14

1 Answers1

0

Add the following before the line where you paste the results:

$TRgWkBk.worksheets.item(1).range("A1").Select

You need to select the cell before pasting in the contents.

And, consider taking off the semi-colons. You only use them if you're putting more than one command on the same line, and you're not doing that here.

Adam
  • 3,891
  • 3
  • 19
  • 42
  • Thank you, that worked. Any idea how to paste to the first blank cell in a specific column? – irishombian Feb 11 '19 at 14:26
  • Adapt the VBA from the post https://stackoverflow.com/questions/14957994/select-first-empty-cell-in-column-f-starting-from-row-1-without-using-offset and reference the answer from CameronFace. – Adam Feb 11 '19 at 14:35