Moving Data to Another Sheet Based on Dropdown Selection

jeff88

New Member
Joined
Jan 25, 2024
Messages
16
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Attachments

  • Excel Data Snip.JPG
    Excel Data Snip.JPG
    31.8 KB · Views: 50

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
@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?
 
Upvote 0
Unfortunately, I have no experience with Office Scripts. :(
 
Upvote 0
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:

JavaScript:
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);
}
 
Upvote 0
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.
 

Attachments

  • Javascript error.JPG
    Javascript error.JPG
    9.5 KB · Views: 5
Upvote 0
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:

JavaScript:
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);
}
 
Upvote 0
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.

JavaScript:
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);
    }
  });
}
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top