copy and paste on a variable cell with macro

Question

I need to copy an interval in one sheet (let's say Sheet1!C3:V9), and paste it on another sheet, in a variable position determined by a ADRESS formula,(creating a cell with the coordinates that I want), let's say its the cell Sheet2!A5. I tried using app script to embed INDIRECT function, but I didn't had any luck. In the following code, what I want is substitute the random cell 'H13' with the coordinates in cell Sheet2!A5, and paste values in that cell.

function Macro() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('H13').activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet1'), true);
    spreadsheet.getRange('C3:V9').activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
    spreadsheet.getRange('Sheet1!C3:V9').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

Thanks for help!

docs.google.com/spreadsheets/d/… In sheet1 I pasted the interval I was talking about (sheet2!C3:V9) in cell H132 (coordinates in cell $A$5), i wanted a code that can do that... I'm new to macro and code, I'm learning. — Simone
So, if I understood correctly: If the value in A2 matches one of the values of Sheet2!C3:V9, you want to copy that range into A5? — Jescanellas
No, I want to be able to copy the range Sheet2!C3:V9 and paste it in the cell H132, as described by the coordinates in $A$5. I'm creating a sort of a work calendar, cell A2 is the week number, and that week start in row 132. I wanted a script to "archive" the timesheet in Sheet2!C3:V9 and paste them in the right week. — Simone

Jescanellas Jescanellas · Accepted Answer · 2020-05-18T09:09:19

How about this solution? If someone edits the A2 cell to change the week number to the current one, it will trigger the following script, which will search for this week number in column D. If it finds it, it will copy the values from Sheet2!C3:V9 to the column H, at the correspondent row:

function onEdit(e){

  if (e.range.getA1Notation() == "A2"){ //Only triggers when A2 is edited

    let sprsheet = SpreadsheetApp.getActiveSpreadsheet();
    let sheet1 = sprsheet.getSheetByName("Sheet1");
    let sheet2 = sprsheet.getSheetByName("Sheet2");

    var weekNumbers = sheet1.getRange("D1:D").getValues(); 
    var weekValues = sheet2.getRange("C3:V9").getDisplayValues();

    var currentWeek = e.range.getValue(); //New week number introduced in A2

    for (var i = 0; i < weekNumbers.length; i++){

      if (currentWeek == weekNumbers[i][0]){ //Only true when A2 equals a number in D

        sheet1.getRange(i+1,8,7,20).setValues(weekValues);
        break; //Stops the loop so it doesn't overwrite the following rows        
      }            
    }    
  }  
}

Notes:

The onEdit will only trigger if a user manually edits the Sheet. If the A2 cell is being updated my another Script or Sheet Formula, it won't work.

The first time you will have to run the code from the Script editor in order to accept permissions (it might give an error, it's expected). After that it won't require more interaction and will run automatically.

References:

copy and paste on a variable cell with macro

1 Answers