1

I need some help I can do it in Excel VBA but can't find out how in Excel Online. I have the below example 1 work but it is not dynamic I need it to paste under the last row. If you can help please provide an example or edit the code and resend. Thank you.

//Example 1
  function main(workbook: ExcelScript.Workbook) {
    let data_sheet = workbook.getWorksheet("Data")
    let progr_sheet = workbook.getWorksheet("Programming");
// Paste to range A1 on progr_sheet from range A4:L19 on data_sheet
  progr_sheet.getRange('A4').copyFrom(data_sheet.getRange("A4:L19"), ExcelScript.RangeCopyType.all, false, false);
}

//Example 2 [Below doesn't work just and example of what I am trying to do.]
// function main(workbook: ExcelScript.Workbook) {
//   let data_sheet = workbook.getWorksheet("Data")
//   let progr_sheet = workbook.getWorksheet("Programming");
//   let myUsedRange = progr_sheet.getUsedRange();
//   let lastRow = myUsedRange.getRowCount();
//   console.log(lastRow) +1;
//   // Paste to range column A (lastRow) on progr_sheet from range A4:L19 on data_sheet
//   progr_sheet.getRange('A & lastRow').copyFrom(data_sheet.getRange("A4:L19"), ExcelScript.RangeCopyType.all, false, false);
//}
Carly Adams
  • 13
  • 1
  • 4

2 Answers2

0

I think you got most of the way there. Your issue is this line

  progr_sheet.getRange('A & lastRow').copyFrom(data_sheet.getRange("A4:L19"), ExcelScript.RangeCopyType.all, false, false);

You can fix it by changing this part:

'A & lastRow'

To this:

'A' + lastRow

Just a note, with the way your code is currently structured, it overrides the last row of data in your Programming sheet. To copy the data to the next row, you just need to add 1 to your lastRow variable like so:

let lastRow = myUsedRange.getRowCount() + 1;

Also, for future questions like this, I would also add the Office Scripts tag so that people on there can view your future questions and assist you with them.

Brian Gonzalez
  • 1,178
  • 1
  • 3
  • 15
  • Thank you for the support. The solution you have provided is great. Awesome work. Your explanation of things helps me understand how I when wrong adding the +1 to the "console.log(lastRow) +1;" was never going to do anything looking back at that ‍♂️. – Carly Adams Feb 17 '22 at 04:13
0

I think you may be working with Office Scripts, not Office JS Add-ins.

If I'm right then try this ...

function main(workbook: ExcelScript.Workbook)
{
  let data_sheet = workbook.getWorksheet("Data")
  let progr_sheet = workbook.getWorksheet("Programming");

  // Get the last row in the programming worksheet.
  // We add another 1 because it returns the index which starts from 0 and 
  // I'm not using getRowCount() because if your worksheet doesn't start using
  // from cell A1, your row count may not equal the last actual row.
  let lastRow = progr_sheet.getUsedRange().getLastCell().getRowIndex() + 1;

  console.log(lastRow);

  // Add 1 to the last row so we start from the next blank.
  lastRow = lastRow + 1

  progr_sheet.getRange("A" + lastRow).copyFrom(data_sheet.getRange("A4:L19"));
}

... I couldn't pinpoint all of the issues but this line ...

progr_sheet.getRange('A & lastRow')

... will not work for you. That will literally come out as the string A & lastRow.

Skin
  • 9,085
  • 2
  • 13
  • 29
  • 1
    Thank you for the support. I have removed the Exceljs tag and amended the heading. The solution you have provided is great. Awesome work. – Carly Adams Feb 17 '22 at 04:08