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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 17, 2023

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?

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