Moving Data to Another Sheet Based on Dropdown Selection

[jeff88]

Hello,

I am trying to setup a workbook with two sheets, one for in-progress jobs and one for completed jobs. The in-progress jobs will have 1 row for each job and a column at the end will have a dropdown with a "Complete" option when the job is completed. When a user selects the "Complete" option, Excel will automatically move the row of data into the completed jobs sheet and delete the (now) empty row.

I have seen other threads on the web and this forum with this action (like this one), but I can't seem to get the code to work. I copy/pasted/changed sheets names & columns for what I need, but it doesn't seem to work. I've also tried working out my own code using bits from that link and some other Googled options without any luck.

I have a basic knowledge of code, but not enough to troubleshoot where the issue is.

I'm using Excel web app.

 

[skillilea]

Pretty sure he is using the Code Editor for Office scrpts

 

[mumps]

@skillilea
Thank you for that observation. I have no experience with Office Scripts.
@jeff88
If you are using Office Scripts, check out this link: Differences between Office Scripts and VBA macros - Office Scripts

 

[jeff88]

@t0ny84 @mumps @skillilea

Thank you all for the help. I am indeed trying to use Office scripts not realizing that it was different. As I am trying to use this for the web app, I believe I will need to use scripts to do what I am trying to accomplish.

Does anybody have any insight into how to code for scripts for this project?

 

[mumps]

Unfortunately, I have no experience with Office Scripts.

 

[skillilea]

Jeff88

I'm pretty sure it's doable. I've seen some elaborate scripts.

I would repost a thread: "How to transfer data between two worksheets using Office Scripts"

It's basically JavaScript knocked down. Probably something like this:

function main(workbook: ExcelScript.Workbook) {
  let sourceSheet = workbook.getWorksheet("Sheet1");
  let targetSheet = workbook.getWorksheet("Sheet2");
 
  let sourceRange = sourceSheet.getRange("A1:C10");
  let valuesToTransfer = sourceRange.getValues();
 
  let targetRange = targetSheet.getRange("A1");
 
  targetRange.setValues(valuesToTransfer);
}

 

[jeff88]

Jeff88

I'm pretty sure it's doable. I've seen some elaborate scripts.

I would repost a thread: "How to transfer data between two worksheets using Office Scripts"

It's basically JavaScript knocked down. Probably something like this:

I started a new thread. I changed the variables to my sheet names and range. Your code actually almost worked. Only one error. I took a snip.

 

[skillilea]

Sorry, I don't know a ton about it. I'm just using intellisense. Post the code and see if someone can clean it up.

Here is one more option:

function main(workbook: ExcelScript.Workbook) {
  let sourceSheet = workbook.getWorksheet("Sheet1");
  let targetSheet = workbook.getWorksheet("Sheet2");
  
  let sourceRange = sourceSheet.getRange("A1:C10");
  let valuesToTransfer = sourceRange.getValues();
 
  let targetRange = targetSheet.getRange("A1:C10"); 
  
  targetRange.clear();

  targetRange.set(valuesToTransfer);
}

 

[t0ny84]

Try this, it is a conversion of mumps script using ChatGPT. Absolutely no guarantees but if it doesn't work it should give you a start.

function Worksheet_Change(e) {
  if (e.range.getCountLarge() > 1) return;
  if (e.range.getColumn() !== 10) return;

  Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const target = e.range;

    await context.sync();

    if (target.getText() === "Complete") {
      const completedJobsSheet = context.workbook.worksheets.getItem("Completed Jobs");
      const lastRow = completedJobsSheet.getRange("A" + completedJobsSheet.getUsedRange().getRowCount()).getEntireRow();
      
      target.getEntireRow().copyTo(lastRow.getOffsetRange(1, 0), Excel.RangeCopyType.all);
      target.getEntireRow().delete(Excel.DeleteShiftDirection.up);
    }
  });
}