Copy cell range

[roelsanjose]

Hello,

Good day!
Can I seek help on how to copy cell range from WorksheetA to WorksheetB in the same workbook?

Appreciate your help.
Thank you.

[dfinke]

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$xlfile = "./test.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue

$data | Export-Excel $xlfile -WorksheetName WorksheetA
$data | Export-Excel $xlfile -WorksheetName WorksheetB 

$excel = Open-ExcelPackage $xlfile

$excel.WorksheetA.Cells["A3:B5"].Copy($excel.WorksheetB.Cells["G3"])

Close-ExcelPackage $excel -Show

[roelsanjose]

Really appreciate the response. It is very helpful. It is able to copy cell range from one worksheet to another.
However, there's one more help I need.

Source formula from WorksheetA - =IFERROR(INDEX('North Europe'!$J:$J,MATCH('NE-Volumes'!$C3,'North Europe'!$B:$B,0)),"")
But when it is copied to WorksheetB - =IFERROR(INDEX('North Europe'!$J:$J,MATCH('NE-Volumes'!$C2,'North Europe'!#REF!,0)),"")

$B:$B was not copied as is and it is replaced by #REF!

Hope you can help me again :)
Thank you.

[dfinke]

Please post the excel file and a PowerShell script that is doing that. Something that reproduces the issue.

[roelsanjose]

Capacity and Utilization Data_Copy.xlsx

Attached is the excel file.
Below is the powershell script.

Copy data to column J

$pathA = “C:\Data\Capacity and Utilization Data_Copy.xlsx”

Export-Excel $pathA -WorksheetName NE-Temp-DoNotDelete
Export-Excel $pathA -WorksheetName NE-Volumes

$NEexcel2 = Open-ExcelPackage $pathA

$NEexcel2."NE-Temp-DoNotDelete".Cells["M2:M1000"].Copy($NEexcel2."NE-Volumes".Cells["J2"])

Close-ExcelPackage $NEexcel2 -Show

Basically, I want to copy column M from NE-Temp-DoNotDelete to column J of NE-Volumes

Thanks

[dfinke]

I don't know why the Copy doesn't seem to work. It is not the PowerShell code. Could be the library I use to create the xlsx, which I don't have control over.

This worked:

$excel = Open-ExcelPackage ".\Capacity.and.Utilization.Data_Copy - Copy.xlsx"

2..1000 | ForEach-Object {
    $Formula = "IFERROR(INDEX('North Europe'!`$J:`$J,MATCH('NE-Volumes'!`$C{0},'North Europe'!`$B:`$B,0)),`"`")" -f $_
    Set-ExcelRange -Worksheet $excel.'NE-Volumes' -Range "J$($_)" -Formula $Formula
}

Close-ExcelPackage $excel -Show

[roelsanjose]

@dfinke it is a big thanks to you. really appreciate your time and effort. everything is working now.