Forum Discussion

RyanExcels's avatar
RyanExcels
Copper Contributor
Oct 17, 2023

Copy & Paste Based On Header

Hi im trying to copy everything in column "Property Name" on  Sheet "M" and paste special values into Sheet "P" Into the column named "Property Name". Sometimes these columns may change order but the...
excel
RyanExcels's avatar
RyanExcels
Copper Contributor
Oct 18, 2023
Hi i have one main spreadsheet which isn't in a table it has multiple 'Headers'/text. I want to populate another sheet using the data under this header. Sometimes there will be formulas in the master file hence the paste values.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 21, 2023

RyanExcels 

That could be

function main(workbook: ExcelScript.Workbook) {

    const header = "Property Name"
    const source = workbook
        .getWorksheet("M")
        .findAll(header, { completeMatch: true })
        .getAreas()[0]
        .getExtendedRange(ExcelScript.KeyboardDirection.down)
    const target = workbook
        .getWorksheet("P")
        .findAll(header, { completeMatch: true })
        .getAreas()[0]
        .getExtendedRange(ExcelScript.KeyboardDirection.down)

    target.clear()
    target.copyFrom(source, ExcelScript.RangeCopyType.values)
}

We find first appeared in each sheet cell with "Property Name" text in it. In source sheet get range till first empty cell in the column down. If you assume that could be blank cells within the range we need to use another logic.

In the target sheet we clear all cells in the range found by similar way as in source, and paste here values from the source.

Copy/pasting includes header. If header in target has some formatting and you'd like to keep it we need to exclude first row from source/target ranges (e.g. with getOffsetRange ).

Resources