从一个选项卡复制非空范围，然后粘贴到另一选项卡的末尾，然后将公式向下复制到粘贴范围的右侧 [英] Copy non-empty Range from one tab, and paste at end of another tab, and copy down formulas to the right of the paste range

问题描述

我用它来将非空单元格从一个选项卡(CopyFromTab)复制到另一个选项卡(PasteToTab). 但是，当它向PasteToTab添加新行时... 粘贴内容右侧(在PasteToTab上)的公式不会向下复制(在PasteToTab上).

I used this to copy the non-empty cells from One tab (CopyFromTab), to another tab (PasteToTab). However, when it adds new rows to the PasteToTab... the formulas to the right of the pasted content (on the PasteToTab) do not copy down (on the PasteToTab).

是否有办法将公式向下复制到PasteToTab上的新行中(这些需要向下复制的公式位于使用粘贴内容中数据的右侧单元格中)

function CopyNotEmptyToDifferentTab(){
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CopyFromTab");  //Copy from here
var range = ss.getRange(8,11,10,6);     //You should be using .getRange(row, column, numRows, numColumns)

  var nonEmpty = range.getValues().filter(function(row) {           // filter matrix for rows
        return row.some(function(col) { // that have at least one column
          return col !== "";});});      // that is not empty



  if (nonEmpty) {

    var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PasteToTab"); //Paste here

    sh2.getRange(sh2.getLastRow()+1,1, nonEmpty.length, nonEmpty[0].length).setValues(nonEmpty);
  } }

参考: 将非空范围复制到新表中，然后向下拖动公式

这是工作脚本:将行粘贴到浆糊的底部，从COPYFROM浆糊中粘贴数据，并复制配方..谢谢您的帮助@Stykes

THIS IS THE WORKING SCRIPT THAT: ADDS ROWS TO BOTTOM OF PASTE SHEET, PASTES DATA FROM COPYFROM SHEET, AND COPIES DOWN FORMULAS.. THANK YOU FOR YOUR HELP @Stykes

function CopyNotEmptyToDifferentTab(){

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CopyFromTab");  //Copy from here

var range = ss.getRange(8,11,10,6);     //You should be using .getRange(row, column, numRows, numColumns)

  var nonEmpty = range.getValues().filter(function(row) {           // filter matrix for rows
        return row.some(function(col) { // that have at least one column
          return col !== "";});});      // that is not empty

  if (nonEmpty) {


    var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PasteToTab"); //Paste here
   var lastRowNumber= sh2.getLastRow();  //GETS LAST ROW AS VARIABLE
    sh2.getRange(sh2.getLastRow()+1,1, nonEmpty.length, nonEmpty[0].length).setValues(nonEmpty);

    var sourceRange = sh2.getRange(lastRowNumber,7,1,6);  //You should be using .getRange(row, column, numRows, numColumns)

sourceRange.autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);



  } }

推荐答案

使用: autoFillToNeighbor()

   //range you would like to copy down can be one cell or multiple
   var sourceRange = sheet.getRange("B1:B4");

   // Results in B5:whereeverColumnAEnds 
  sourceRange.autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

创建的范围必须包括具有公式的最后一行.对于上面的代码，如果B5中包含公式，则此代码将不起作用.相反，范围将需要为"B1:B5".或只是"B5"

The range created must include the last row that has the formula. For the code above, if B5 has a formula in it, this code will not work. Instead the range would need to be "B1:B5" or just "B5"

这篇关于从一个选项卡复制非空范围，然后粘贴到另一选项卡的末尾，然后将公式向下复制到粘贴范围的右侧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！