Copy & Paste Based On Header

[RyanExcels]

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 header name will always be the same. Sometimes the number of rows will vary but it will never exceed 2000 rows

This is using Office Scripts Instead. 

 

Please help!

 

[#2]

@RyanExcels 

Under the column "Property Name" do you mean column in structured table or the text on the top of some range in the sheet? If structured table is it its name predefined; if range in which row(s) of the sheet such text (considering as the header) could appear?

You copy everything in such column till end, but paste into another sheet only special values. What are such special values, what is the logic to select them?

Above probably means we paste only values without formatting properties, correct?

When paste shall we overwrite existing values in target range (and perhaps clean it before since from run to run size could be different) or we append pasted values to the end?

[#3]

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.

[#4]

@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 ).