cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gpa774
New Member

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

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

8 REPLIES 8
VJR
Super User
Super User

Hi @gpa774 

 

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

 

 

gpa774
New Member

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, falsefalse);
}
carlycarlyle
New Member

I also looking for help with this.

carlycarlyle
New Member

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...

carlycarlyle
New Member

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(00).getOffsetRange(10);
  } else {
    startCell = sheet.getRange('A1');
  }
  console.log(startCell.getAddress());
  const targetRange = startCell.getResizedRange(0, data.length - 1);
  targetRange.setValues([data]);
  return;
}

 

carlycarlyle
New Member

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

carlycarlyle
New Member

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, falsefalse);
  }
carlycarlyle
New Member

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-works...

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (2,869)