Forum Discussion
How to fill a range of Sheet A with data from Sheet B by repeating this action for each value
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 } }
****I updated the screenshot because there was sensitive information.*****
Values copied from Sheet B are text and all of them are different. However, all Sheet A values are the same. It's like I'm making new tables in Sheet A as many values there are in sheet B by copying and pasting them into the column C of Sheet A.
So, I think that I need a macro to do that because I used a script to record my actions unfortunately it doesn't work:
- Copy Data1 from Sheet B
- Paste it into column C of Sheet A
- Copy the range A2:H10 of SheetA with the new values added into the column C
- Paste it below
- Replace the Data1 previously copied by Data2
- and repeat step 3-5 for Data3 and so on.
Do you have any idea how can I improve the script or create the macro?
I don't see any reason why m_tarler 's solution does not accomplish this task. If each item is not repeated a fixed amount of times then something more sophisticated would need to be done. From what I gather with your example, it's fixed at 9 times each.
Ok I understand but it doesn't work for me.
