Copying array values and pasting to a range

[minishep]

Hi there,

I have compiled an array of names of players, and would like to set up a button click to copy each value of the array and paste the value to the column beside it.

The reason for this is that the list of players is dynamic and when I update the power query links, I would like to be able to just press the button to copy over the names instead of having to select the entire array and copy-paste.

To explain a little of what I am doing: I have a spreadsheet containing players from the years 2019, 2020, and 2021. I then created an array which compiles all unique names (i.e. displaying all players from 2019-2021 1 time each).

Now I would like to set up a table which contains specific values of each player, which I will use VLOOKUP to find. However, I cannot make a dynamic table range with an array, so I need to copy and paste the array values to cells. I would like to not have to manually copy the array values and paste them into the table.

The idea was to use a button that will do a loop through each array value and copy-paste it to a cell. Currently there are just over 1200 entries.

Thank you kindly

 

[jdellasala]

First, I would highly recommend using XLOOKUP instead of VLOOKUP.
I totally agree that not being able to use in a data Table is frustrating, however many of the functions can probably assemble the formula - probably in one cell (which is kind of too out there, but fun!).
Functions that might help are FILTER, HSTACK, VSTACK, TEXTSPLIT, TEXTJOIN, LET, and many others. As for formatting, Conditional Formatting can help the result look just like a real data Table!
Mike Girvin's video Single Cell Excel Reporting... on his ExcelIsFun YouTube channel might fit the bill.