Office Script to Copy Rows From One Worksheet To Another

[SlightlyClueless]

I have a shared excel workbook on SharePoint that is being used by a few users. This sheet tracks reach outs to counties across the United States. When a reach out turns into a lead it then gets copied to a separate worksheet to expand on from there. I attempted to write a script that would accomplish this but failed miserably. What I am trying to accomplish with this script is in the "US Counties" worksheet, column O will have the option to move that row to the "RSR" worksheet. The script would then copy data from that row and place it in a new row in the "RSR" worksheet. There is a little shift in the columns between sheets which I have mapped out in the table below. Any help or direction is greatly appreciated.

Sample Sheet - https://widewaters.sharepoint.com/:x...kDOnA?e=Tx0isn

Cross Posted Links
Stack Overflow - https://stackoverflow.com/questions/...37441_78798715
Mr.Excel - https://www.mrexcel.com/board/thread...other.1262179/

COUNTIES.png

0bL5bmHC.png

[Logit]

Please post a sample copy of your workbook with a few example entries. Images are nice but very difficult to work with. Thanks.

[SlightlyClueless]

Link to a sample sheet - https://widewaters.sharepoint.com/:x...kDOnA?e=Tx0isn

[AliGW]

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. No further help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)

[SlightlyClueless]

My apologies about that! I have modified the original post to include link to the 2 other sites the question has been posted to.

[ByteMarks]

Something along these lines, perhaps.

function main(workbook: ExcelScript.Workbook,Confirm: string = "Y") {
// Get the source worksheets
    let sourceSheet = workbook.getWorksheet("US Counties");
    let targetSheet = workbook.getWorksheet("RSR");

//check that the sheets exist and that the active sheet is the source sheet
    if (targetSheet == undefined) {
        console.log("RSR sheet doesn't exist");
        return;
    }

    if (sourceSheet == undefined) {
        console.log("US Counties sheet doesn't exist");
        return;
    }
    console.log(workbook.getActiveWorksheet().getName());
    if (workbook.getActiveWorksheet().getName() != sourceSheet.getName()) {
        console.log("Please select the cell in Us Counties whose row you wish to copy");
        return;
    }
//Get the copy and paste rows on the respective sheets
    var r = targetSheet.getRange("A1").getSurroundingRegion().getLastRow().getRowIndex()+2;
    var ar = workbook.getActiveCell().getRowIndex() + 1;

//Assign the Copy/Paste ranges to variables
    var rngToCopy1 = sourceSheet.getRange("A" + ar  + ":B" + ar);
    var rngToPaste1 = targetSheet.getRange("B" + r + ":C" + r);

    var rngToCopy2 = sourceSheet.getRange("G" + ar + ":H" + ar);
    var rngToPaste2 = targetSheet.getRange("J" + r + ":K" + r);

    var rngToCopy3 = sourceSheet.getRange("L" + ar + ":N" + ar);
    var rngToPaste3 = targetSheet.getRange("L" + r + ":N" + r);
 
    var rngToCopy4 = sourceSheet.getRange("P" + ar + ":R" + ar);
    var rngToPaste4 = targetSheet.getRange("W" + r + ":Y" + r);
    
//Copy the ranges
    rngToPaste1.copyFrom(rngToCopy1);
    rngToPaste2.copyFrom(rngToCopy2);
    rngToPaste3.copyFrom(rngToCopy3);
    rngToPaste4.copyFrom(rngToCopy4);

    console.log("Done")
}