Pasting to Excel using Powershell

[ExcelOnFire]

I am using Powershell to take information from one report and adding it to another. I have been able to achieve this goal with one small hiccup that I cannot figure out. The script will open both workbooks and copy the selected cells. The problem that I am stuck on is how to paste using the destination formatting. This below script will paste but using the source format.

Any help would be greatly appreciated.

Can you take a look at this code, I am having a similar problem with pasting. The code will perform as written but it is keeping the source format and I want the integrity of the destination formatting to be maintained. Any help will be greatly appreciated!

#This opens a new Excel object and makes it visible

$excel
=new-object-comobjectexcel.application;

$excel
.visible=$true;


#This identifies the source workbook (the one from which the data will be copied).

$SourceWorkBook
=$Excel.Workbooks.open("C:\Users\mdelaney\Downloads\CCOPCL b0144535.xlsx");


#This activates the source workbook

$SourceWorkBook
.WorkSheets.item("Page2_2").activate();


#This is a 5 second pause to allow the workbook to open

Start-Sleep
-s5


#This specifies the range that we want to copy

$SourceRange
=$SourceWorkBook.WorkSheets.item("Page2_2").range("R5");


#This copies that range. It will say "true" if the copy is successful, but we're not interested in seeing that.

#Because of this, we will use the out-null cmdlet, which discards any info that is piped to it.

$SourceRange
.copy() |out-null;


#This identifies the target workbook (the one to which the data will be pasted)

$TargetWorkBook
=$excel.workBooks.Open("C:\Users\mdelaney\Desktop\November DC-MM SLA Report.xlsx");


#This is a 5 second pause to allow the workbook to open

Start-Sleep
-s5


#This identifies the destination where we want the information copied.

#Here, it pastes the information in the tab named "SLA Report" of the target workbook

#First, identify where you want it pasted

$TargetRange
=$TargetWorkbook.Worksheets.item("SLA Report").range("B11");


#Then, paste that into the target workbook

$TargetRange
.Activate()|Out-Null; $Excel.ActiveSheet.Paste()


　

　

　

　

#This specifies the range that we want to copy

$SourceRange
=$SourceWorkBook.WorkSheets.item("Page2_2").range("G5");


#This copies that range. It will say "true" if the copy is successful, but we're not interested in seeing that.

#Because of this, we will use the out-null cmdlet, which discards any info that is piped to it.

$SourceRange
.copy() |out-null;


#This identifies the destination where we want the information copied.

#Here, it pastes the information in the tab named "SLA Report" of the target workbook

#First, identify where you want it pasted

$TargetRange
=$TargetWorkbook.Worksheets.item("SLA Report").range("C11");


#Then, paste that into the target workbook

$TargetRange
.Activate()|Out-Null; $Excel.ActiveSheet.Paste()


　

　

　

#This specifies the range that we want to copy

$SourceRange
=$SourceWorkBook.WorkSheets.item("Page2_2").range("N5");


#This copies that range. It will say "true" if the copy is successful, but we're not interested in seeing that.

#Because of this, we will use the out-null cmdlet, which discards any info that is piped to it.

$SourceRange
.copy() |out-null;


#This identifies the destination where we want the information copied.

#Here, it pastes the information in the tab named "SLA Report" of the target workbook

#First, identify where you want it pasted

$TargetRange


=$TargetWorkbook.Worksheets.item("SLA Report").range("D11");


#Then, paste that into the target workbook

$TargetRange


.Activate()|Out-Null; $Excel.ActiveSheet.Paste()