Forum Discussion
Dan1ExcelUser
Feb 05, 2025Copper Contributor
How to fill a range of Sheet A with data from Sheet B by repeating this action for each value
Hi, I have two sheets A & B. Sheet A has a table where A1:H1 is the header. The range C2:C9 of this table is blank but there is data in the ranges A2:B9 and D2:H9. Sheet B has data in the range A...
- Feb 10, 2025
As variant
function main(workbook: ExcelScript.Workbook) { const data = workbook.getWorksheet("SheetB") .getUsedRange().getValues().slice(1) const range = workbook.getWorksheet("SheetA") .getRange("A2:H10") let i = 0 for (let item of data) { let next = range.getOffsetRange(9 * i, 0) i > 0 ? next.copyFrom(range) : '' next.getColumn(2).setValues(item[0]) i += 1 } }
SergeiBaklan
Feb 10, 2025Diamond Contributor
As variant
function main(workbook: ExcelScript.Workbook) {
const data = workbook.getWorksheet("SheetB")
.getUsedRange().getValues().slice(1)
const range = workbook.getWorksheet("SheetA")
.getRange("A2:H10")
let i = 0
for (let item of data) {
let next = range.getOffsetRange(9 * i, 0)
i > 0 ? next.copyFrom(range) : ''
next.getColumn(2).setValues(item[0])
i += 1
}
}
Dan1ExcelUser
Feb 12, 2025Copper Contributor
SergeiBaklanyour formula works but there are three extra rows between each tables after it pasted the range A2:H10 with the cell values it copied from Sheet B.
Many thanks!
- SergeiBaklanFeb 12, 2025Diamond Contributor
Dan1ExcelUser , in my test I have such data in SheetA
and in SheetB
assuming rows below are blank, not ones with empty strings ""
Result is without extra rows
If problem still exists perhaps you may share workbook with actual data structure removing sensitive information.
- Dan1ExcelUserFeb 17, 2025Copper Contributor
I've updated your formula to meet my needs. I understand how it works and I've added/removed columns/rows in both sheets to copy/paste data with it. Thanks a lot!