Unanswered

Copy Range from worksheet and paste it on last row of another sheet

(0) Share

Report

Posted on by Community Power Pla...

Hi ,

Our Company recently migrated from google to MS. I am new to MS power automate. I am looking for solution to create a script / flow to copy from range or table and paste it on last row of another sheet . I manage to convert google script to VBA but I am having issue with excel online script . Any help will be appreciated.

Source Sheet - Shift Logs-PM6 Target Sheet - Mill Issues

Working google script:

function moveDataR() {

// Get handles to Daily and Archive sheets

var dailySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Shift Logs-PM6');

var appendSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Mill Issues');

// Create range strings for the rows in Daily and Archive sheets

var dailySheetRange = "Shift Logs-PM6!11:" + dailySheet.getLastRow();

var archiveLastRow = dailySheet.getLastRow() + appendSheet.getLastRow();

var archiveAppendRange = "Mill Issues!" + (appendSheet.getLastRow() + 1) + ":" + archiveLastRow;

// Get range of data to copy

var destRange = dailySheet.getRange(archiveAppendRange);

// Copy the data to the archive sheet

var sourceDataValues = dailySheet.getRange(dailySheetRange).copyTo(destRange, {contentsOnly: true});

}

Current VBA code working on desktop excel:

Sub CopyActiveRow()
Range("B11:G26").Copy
With Sheets("Mill Issues").Range("A" & Rows.Count).End(xlUp).Offset(1)
.PasteSpecial (xlPasteAll)
.PasteSpecial (xlPasteValues)
ActiveWindow.SmallScroll Down:=-15
Range("F4").Select
End With
End Sub

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (0)

VJR 7,635 on at

Like (0)

Report

Hi @Anonymous

ExcelScripts also have a recorder just like the macro recorder in VBA.
Could you try to generate code out of it.
Recorder in Excel Office Scripts

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @VJR ,
I tried recording script. I can't figure out correct syntax for getting last row.
Below script recorded .

function main(workbook: ExcelScript.Workbook) {
  let mill_Issues = workbook.getWorksheet("Mill Issues");
  let selectedSheet = workbook.getActiveWorksheet();
  // Paste to range A622 on mill_Issues from range B11:G26 on selectedSheet
  mill_Issues.getRange("A622").copyFrom(selectedSheet.getRange("B11:G26"), ExcelScript.RangeCopyType.values, false, false);
}

Was this reply helpful? Yes No
CA-01090606-0 20 on at

Like (0)

Report

I also looking for help with this.

Was this reply helpful? Yes No
CA-01090606-0 20 on at

Like (0)

Report

I found this but still don't know how you paste rage A1:L19 from sheet 2 to +1 of the rest row in sheet 1

https://stackoverflow.com/questions/66224473/office-js-is-there-a-way-to-find-the-last-row-with-data-in-on-an-excel-sheet-so

Was this reply helpful? Yes No
CA-01090606-0 20 on at

Like (0)

Report

Also found this.
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet('Programming');
  const data = ['2016', 'Bikes', 'Seats', '1500', .05];
  addRow(sheet, data);
  return;
}

function addRow(sheet: ExcelScript.Worksheet, data: (string | number | boolean)[]): void {

  const usedRange = sheet.getUsedRange();
  let startCell: ExcelScript.Range;
  // IF the sheet is empty, then use A1 as starting cell for update
  if (usedRange) {
    startCell = usedRange.getLastRow().getCell(0, 0).getOffsetRange(1, 0);
  } else {
    startCell = sheet.getRange('A1');
  }
  console.log(startCell.getAddress());
  const targetRange = startCell.getResizedRange(0, data.length - 1);
  targetRange.setValues([data]);
  return;
}

Was this reply helpful? Yes No
CA-01090606-0 20 on at

Like (0)

Report

Can someone help and provide an example. Please 😁 I'm very new to everything.

Was this reply helpful? Yes No
CA-01090606-0 20 on at

Like (0)

Report

This is what I have but it is wrong don't know how to make it work.

function main(workbook: ExcelScript.Workbook) {
  let data = workbook.getWorksheet("Data");
  let selectedSheet = workbook.getWorksheet("Programming");
  let myUsedRange = selectedSheet.getUsedRange();
  let lastRow = myUsedRange.getRowCount();
  console.log(lastRow);
  // Paste to range LastRow on selectedSheet from range A4:L19 on data
  selectedSheet.getRange[lastRow].copyFrom(data.getRange("A4:L19"), ExcelScript.RangeCopyType.all, false, false);
  }

Was this reply helpful? Yes No
CA-01090606-0 20 on at

Like (0)

Report

The solution to my issue is provided in the link below thanks, I understand how to do it now.
I hope it helps someone else.

https://stackoverflow.com/questions/71150366/excel-online-office-scripts-copy-range-a1j10-from-worksheet2-and-paste-it-on

Was this reply helpful? Yes No