Copy and Paste Rows to another worksheet based on a cells value

Question

Summary: I have a tab called "Working List", in column AI is a Yes/No/Blank field. Once a day (may use a button, may create a power automate flow, not sure yet), we want to run a script that moves the "Yes" rows to another sheet called "Completed" (paste as values).

I am super new to script and not great at VBA (working on it), so I have read as much as I could but am not quite understanding how to make this work. I originally had a macro setup but quickly learned that online workbooks don't like that...

The struggle mainly has been that the "working list" tab (Working List Tab); columns B - AH is a pivot table (power query connection to a Power BI data set). Column A & AI - AN are tables just because I wanted to see if that made a difference (Columns A = table name "Claimed"; AI - AN = table name "Completed_WL"). The "Completed" tab is fully a table (table name - table2), in case that makes a difference.

I need the full row A - AN to be pasted as values. I don't need it to delete the rows - once the data set updates it will filter out.

I have tried using the "record" option, but it only copies and pastes the first row. I am not sure what to add/change to copy visible only:

function main(workbook: ExcelScript.Workbook) {
    let completed_WL = workbook.getTable("Completed_WL");   
    completed_WL.getColumnByName("Completed").getFilter().applyValuesFilter(["Yes"]);
    let table2 = workbook.getTable("Table2");
    let selectedSheet = workbook.getActiveWorksheet();
                   table2.getColumn("Claimed").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(selectedSheet.getRange("A5:AN5"), ExcelScript.RangeCopyType.values, false, false);
        completed_WL.getColumnByName("Completed").getFilter().clear();
}

Any help would be greatly appreciated!!

Brian Gonzalez · Answer 1 · 2023-01-15T02:20:27.303

0

You can try updating your code to use a variable to get the last row. After you did that, you can integrate that into your copy statement to copy the data. You can see how that would look in the code below:

    function main(workbook: ExcelScript.Workbook) {
      let completed_WL = workbook.getTable("Completed_WL");
      completed_WL.getColumnByName("Completed").getFilter().applyValuesFilter(["Yes"]);
      let table2 = workbook.getTable("Table2");
      let selectedSheet = workbook.getActiveWorksheet();
      let lastRow = selectedSheet.getCell(selectedSheet.getRange().getRowCount() - 1, 0).getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex()+1;
      table2.getColumn("Claimed").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(selectedSheet.getRange(`A5:AN${lastRow}`), ExcelScript.RangeCopyType.values, false, false);
      completed_WL.getColumnByName("Completed").getFilter().clear();
    }

edited Jan 15 '23 at 02:20

answered Dec 21 '22 at 22:36

Brian Gonzalez

1,178
1
3
15

This is great! I am getting an error at line 7 "Line 7: Worksheet getRange: The argument is invalid or missing or has an incorrect format." `table2.getColumn("Claimed").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(selectedSheet.getRange(`A5:AN${lastRow}`), ExcelScript.RangeCopyType.values, false, false); ` I'm trying to see where it's invalid but I'm not sure on formatting with script on that is acceptable yet. – Jess Dec 22 '22 at 18:34
Can you let me know what the value of the lastRow variable is? You can do that by writing `console.log(lastRow)`. Put this line in a line after the lastRow variable is assigned but before the line where you're getting the error. – Brian Gonzalez Dec 22 '22 at 18:57
type: "Range", instance: Object, engine: Object} type: "Range" instance: Object engine: Object – Jess Dec 22 '22 at 20:37
OOOO I had an idea!! Since the copy paste works just fine - I can use power automate to delete a row after I run the script! – Jess Dec 22 '22 at 22:01
Ah I see the error now. I needed to add `.getRowIndex()+1` to the end of lastRow variable. I will update my code. You can also try with PowerAutomate. – Brian Gonzalez Dec 22 '22 at 22:11

Copy and Paste Rows to another worksheet based on a cells value

1 Answers1