I'm trying to set up a macro to run once a month to collect current figures from a set of formulas, but I'm struggling to get it to select the right sheet before running the copy and paste commands.

function RecordCurrentFigures() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getSheetByName('Current Figures');
  spreadsheet.getRange('9:9').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('B9').activate();
  spreadsheet.getRange('B5:F5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  spreadsheet.getRange('B5:F5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

CodePudding user response：

I think the issue is that you are calling it but you're not using it afterwards, you are using getActiveSheet and getActiveRange instead.

Try:

function RecordCurrentFigures() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Current Figures');
  var range = sheet.getRange('9:9')
  sheet.insertRowsBefore(range.getRow(), 1);
  // range.offset(0, 0, 1, range.getNumColumns()); // this is a NOP
  var range2 = sheet.getRange('B9');
  sheet.getRange('B5:F5').copyTo(range2, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  sheet.getRange('B5:F5').copyTo(range2, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}