Automate Script Help Needed

[CoplandA]

Good morning,


I am working thru a problem and I cannot use VBA as that will disallow autosave to be on (which our company requires on this specific document). I should preface to say that I have never experimented with scripting in any way.

I have two tables on two separate sheets. I would like to MOVE completed jobs from "Job" table to "CompletedJobs" table.

The column for criteria of Complete, which will simple have a "Y" is U:U.

This issue is causing me lots of problems and I just cannot figure it out. Can anyone help?

Having any set filters within the script is not an absolute requirement.

The script that i have input is not returning any rows. Not sure what needs to be adjusted.

Test - Google Drive

-------------------------------------------------
/*
This script does the following:
Selects rows from the source table where the value in a column is equal to some value (FILTER_VALUE in the script).
Moves all selected rows into the target table in another worksheet.
Reapplies the relevant filters to the source table.
*/
function main(workbook: ExcelScript.Workbook) {
// You can change these names to match the data in your workbook.
const TARGET_TABLE_NAME = "CompletedJobs";
const SOURCE_TABLE_NAME = "Job";
// Select what will be moved between tables.
const FILTER_COLUMN_INDEX = 21;
const FILTER_VALUE = "Y";
// Get the Table objects.
let targetTable = workbook.getTable(TARGET_TABLE_NAME);
let sourceTable = workbook.getTable(SOURCE_TABLE_NAME);
// If either table is missing, report that information and stop the script.
if (!targetTable || !sourceTable) {
console.log(
`Tables missing - Check to make sure both source (${TARGET_TABLE_NAME}) and target table (${SOURCE_TABLE_NAME}) are present before running the script. `
);
return;
}

// Get all the data from the table.
const sourceRange = sourceTable.getRangeBetweenHeaderAndTotal();
const dataRows: (
| number
| string
| boolean
)[][] = sourceTable.getRangeBetweenHeaderAndTotal().getValues();
// Create variables to hold the rows to be moved and their addresses.
let rowsToMoveValues: (number | string | boolean)[][] = [];
let rowAddressToRemove: string[] = [];
// Get the data values from the source table.
for (let i = 0; i < dataRows.length; i++) {
if (dataRows[FILTER_COLUMN_INDEX] === FILTER_VALUE) {
rowsToMoveValues.push(dataRows);
// Get the intersection between table address and the entire row where we found the match. This provides the address of the range to remove.
let address = sourceRange
.getIntersection(sourceRange.getCell(i, 0).getEntireRow())
.getAddress();
rowAddressToRemove.push(address);
}
}
// If there are no data rows to process, end the script.
if (rowsToMoveValues.length < 1) {
console.log(
"No rows selected from the source table match the filter criteria."
);
return;
}
console.log(`Adding ${rowsToMoveValues.length} rows to target table.`);
// Insert rows at the end of target table.
targetTable.addRows(-1, rowsToMoveValues);
// Remove the rows from the source table.
const sheet = sourceTable.getWorksheet();
// Remove all filters before removing rows.
sourceTable.getAutoFilter().clearCriteria();
// Important: Remove the rows starting at the bottom of the table.
// Otherwise, the lower rows change position before they are deleted.
console.log(
`Removing ${rowAddressToRemove.length} rows from the source table.`
);
rowAddressToRemove.reverse().forEach((address) => {
sheet.getRange(address).delete(ExcelScript.DeleteShiftDirection.up);
});

}

 

[Flaiban]

Anything , only adjust :

Sub MoveBasedOnValue()

Dim xRg As Range
Dim xCell As Range
Dim A As Long
Dim B As Long
Dim C As Long

A = Worksheets("table test").UsedRange.Rows.Count
B = Worksheets("Completed Jobs").UsedRange.Rows.Count

    If B = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("Completed Jobs").UsedRange) = 0 Then B = 0
       
        End If

        Set xRg = Worksheets("table test").Range("U2:U" & A)

        On Error Resume Next

        Application.ScreenUpdating = False

        For C = 1 To xRg.Count
            If CStr(xRg(C).Value) = "Y" Or CStr(xRg(C).Value) = "y" Then
                xRg(C).EntireRow.Copy Destination:=Worksheets("Completed Jobs").Range("A" & B + 1)
                xRg(C).EntireRow.Delete

                If CStr(xRg(C).Value) = "Y" Or CStr(xRg(C).Value) = "y" Then
                C = C - 1

                End If

                B = B + 1

            End If

        Next

Application.ScreenUpdating = True

End Sub

 

[CoplandA]

Good afternoon,

I cannot use VBA in this workbook.

 

[mmirele1]

You need to make your const FILTER_COLUMN_INDEX = 20

The column index starts at 0 for column A not 1.