Forum Discussion

chip1645's avatar
chip1645
Copper Contributor
May 14, 2024

Copying date from one sheet to another sheet using script.

I am using power automate to create a PDF of a worksheet and email to someone.  Now the worksheet if populated by formulas.  Therefore, when power automate pulls the worksheet to creat a PDF the data doesn't come with it.

 

So I figured no big deal, i'll just create a script that will copy the formula filled data to paste the values and formating to another worksheet.

 

Well, after banging my head agains the wall for about 3 hours, I can't seem to get it to work.

 

I was able to come up with this using the record actions button.

 

 

 

function main(workbook: ExcelScript.Workbook) {
	let report8453 = workbook.getWorksheet("Report8453");
	let selectedSheet = workbook.getActiveWorksheet();
	// Paste to all cells on report8453 from all cells on selectedSheet
	report8453.getRange().copyFrom(selectedSheet.getRange(), ExcelScript.RangeCopyType.values, false, false);
	// Paste to all cells on report8453 from all cells on selectedSheet
	report8453.getRange().copyFrom(selectedSheet.getRange(), ExcelScript.RangeCopyType.formats, false, false);
}

 

 

But what I found out real fast is that this only works with what ever worksheet you are currently looking at.  So if you don't watch it, you'll copy and paste to the wrong worksheet and also find out real fast there is no undo.

 

So after going back and restoring to an orginal version of what I am looking at.  I came up with this.

 

 

function main(workbook: ExcelScript.Workbook) {
	let Tn8453 = workbook.getWorksheet("Tn8453");
	let report8453 = workbook.getWorksheet("Report8453");
	// Paste to all cells on report8453 from all cells on selectedSheet
	report8453.getRange("A1:D18").copyFrom(Tn8453.getRange("A1:D18"), ExcelScript.RangeCopyType.values, false, false);
	// Paste to all cells on report8453 from all cells on selectedSheet
	report8453.getRange("A1:D18").copyFrom(Tn8453.getRange("A1:D18"), ExcelScript.RangeCopyType.formats, false, false);
}

 

 

This is the code that I came up with, and it worked for about 2 min, then started getting an error.

 

Line 5: Range copyFrom: There was an internal error while processing the request.

 

What I thought I was doing was telling it to go to worksheet Tn8453 and copy those cells, and then pasting what I needed.  But still get the error above.

 

 

Now at the very least, I just manully did what I wanted, and it came out looking great.

 

 Is there something missing?

 

Thank you for your help.

Chip

No RepliesBe the first to reply