Home > OS >  Using Macros Copy and Paste At Last Row
Using Macros Copy and Paste At Last Row

Time:11-24

I need to copy and paste rows data only in a spreadsheet using Google Macros. But I tring to recording Macro to keep my functions work. And it gives the result just paste a values to the next function( 1) . So However, I wish that paste a new row data at bottom row before would create a new row. And the way is First create a new row at the bottom row Second paste insert any row data that I selected.

function _3333() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveSheet().insertRowsAfter(spreadsheet.getActiveRange().getLastRow(), 1);
  spreadsheet.getActiveRange().offset(spreadsheet.getActiveRange().getNumRows(), 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getCurrentCell().activate();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow() - 1, 1, 1, sheet.getMaxColumns()).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

enter image description here

CodePudding user response:

In your situation, how about the following modification?

From:

sheet.getRange(spreadsheet.getCurrentCell().getRow() - 1, 1, 1, sheet.getMaxColumns()).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

To:

sheet.getRange(spreadsheet.getCurrentCell().getRow() - 1, 1, 1, sheet.getMaxColumns()).copyTo(sheet.getRange(sheet.getLastRow()   1, 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  • In this modification, your getCurrentCell() is copied to the next row of the last row.

  • If you want to copy getCurrentCell() to the last row, please modify it to as follows.

      sheet.getRange(spreadsheet.getCurrentCell().getRow() - 1, 1, 1, sheet.getMaxColumns()).copyTo(sheet.getRange(sheet.getLastRow(), 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    

Added:

From your following replying,

I wish that paste a new row data at bottom row before would create a new row. And the way is First create a new row at the bottom row Second paste insert any row data that I selected.

  1. Insert a new row to the bottom row.
  2. Copy and paste the selected row to the inserted new row.
  3. Those are the same active sheet.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  var sheet =  SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  sheet.insertRowAfter(lastRow);
  var selectedRow = sheet.getRange(sheet.getActiveRange().getRow(), 1, 1, sheet.getLastColumn());
  selectedRow.copyTo(sheet.getRange(lastRow   1, 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}
  • Related