CODEWITHSUNDEEP
excelvbaoffice-scripts
Ben
Ben

Reputation: 35

Is it possible to copy, paste, and delete data from one sheet to another in office scripts?

I have a document saved on SharePoint that documents testing.

Once testing has been completed I would like to move the tests (entire row) to a separate sheet to archive the test schedule, in the next empty row going down, and because its now archived delete it from the submission sheet.

Both sheets are within the same workbook. The number of rows differ each time the script is run therefore the script code would need to work based on a selection input by the user (either prior to running the script or prompted by the script).

I managed this in VBA. I can't locate a viable alternative to the selection function. Is there any way of translating the below VBA code to Office script so the same thing happens in Excel online?

Sub MoveCompletedTests()

     Selection.Copy Sheets("Archive - ATL staff only").Range("A1048576").End(xlUp).Offset(1, 0)

     Selection.Delete

End Sub

I have a button on the "Sample submission" sheet that runs the above code on the selected range on that sheet moving it to the "Archive - ATL staff only" sheet.

I attempted to use the script recorder but the script didn't allow for the selection to be dynamic enough, it coded for the cell range rather than just selection.

Upvotes: 3

Views: 1948

Answers (2)

Brian Gonzalez
Brian Gonzalez

Reputation: 1356

You can try the code here:

    function main(workbook: ExcelScript.Workbook) {
        //Assign the source variable to the selected range
        let source: ExcelScript.Range = workbook.getSelectedRange()
    
        //Assign the archive variable to the Archive worksheet
        let archive: ExcelScript.Worksheet = workbook.getWorksheet("Archive - ATL staff only");
    
        //Set the destination range in the archive sheet
        let destination: ExcelScript.Range = archive.getRange("A:A").getExtendedRange(ExcelScript.KeyboardDirection.up).getLastCell().getOffsetRange(1,0)
    
        //Determine if the offset range is set to A2 in the Archive sheet.
        //If so, determine if A1 has a value. If it does not, update the
        //destination range to cell A1.
    
        if (destination.getAddress() === "'Archive - ATL staff only'!A2" ){
          if (destination.getOffsetRange(-1,0).getValue() === "") {
            destination = destination.getOffsetRange(-1,0)
          }
        }
    
        //Copy the selected source range to the destination range
        destination.copyFrom(source, ExcelScript.RangeCopyType.all);
    
        //Delete the data in the source range
        source.delete(ExcelScript.DeleteShiftDirection.up);
      }

This code determines the last cell based on the data in column A in the archive sheet. This can be useful if the used range of the archive sheet has data somewhere, but column A does not. In this scenario, this code would still update correctly.

Also, this code doesn't copy and delete the entire row. So if you intend to use a selection smaller than the entire row, this code may work better.

Upvotes: 1

Vitalizzare
Vitalizzare

Reputation: 7230

I think that something like getSelectedRange should work (at least, if you are not working with several selected ranges at once):

    function main(workbook: ExcelScript.Workbook)
    {
      let destination: ExcelScript.Range;
      let source = workbook.getSelectedRange().getEntireRow();
      let archive = workbook.getWorksheet("Archive - ATL staff only");
      if(archive.getUsedRange() == undefined) {
        // if the archive sheet is empty, use the first row as a destination
        destination = archive.getRange("1:1");
      }
      else {
        // ... otherwise, use the next row after the last used
        destination = archive.getUsedRange().getRowsBelow().getEntireRow();
      }
      destination.copyFrom(source, ExcelScript.RangeCopyType.values);
      source.delete(ExcelScript.DeleteShiftDirection.up);
    }

p.s. To be clear, in case of VBA a Selection is a property of Application. But in case of ExcelScript it's a method of a Workbook object. This can be a bit confusing because there is an ExcelScript.Application interface, which has no mention of Selection.

Upvotes: 3

Related Questions